02 - SQL

02 - SQL - CSE 135 Database design & SQL programming...

Info iconThis preview shows pages 1–5. Sign up to view the full content.

View Full Document Right Arrow Icon
1 CSE 135 2 App Server Web Application (jsp, servlet …) Applications’ View of a Relational Database Management System (RDBMS) Persistent data structure Large volume of data High-level API for reading (querying) & writing (inserting, deleting, updating) Automatically optimized Transaction management (ACID) Atomicity: all or none happens, Consistency Isolation: appearance of “one at a time” Durability: recovery from failures and other errors RDBMS JDBC Client Relations, cursors, other… JDBC SQL commands RDBMS Server Relational Database
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2 3 Data Structure: Relational Model Relational Databases: Schema + Data Schema: collection of tables (also called relations ) each table has a set of attributes no repeating relation names, no repeating attributes in one table Data (also called instance ): set of tuples tuples have one value for each attribute ID Title Director Actor 1 Wild Lynch Winger 2 Sky Berto Winger 3 Reds Beatty Beatty 4 Tango Berto Brando 5 Tango Berto Winger 7 Tango Berto Snyder Movie ID Theater Movie 1 Odeon 1 2 Forum 3 3 Forum 2 Schedule Slides are modified version of similar example in 132A 4 Data Structure: Primary Keys; Foreign Keys are value-based pointers ID is primary key of Schedule” => its value is unique in Schedule.ID Schedule.Movie is foreign key (referring) to Movie.ID” means every Movie value of Schedule also appears as Movie.ID Intuitively, Schedule.Movie operates as pointer to Movie(s) ID Title Director Actor 1 Wild Lynch Winger 2 Sky Berto Winger 3 Reds Beatty Beatty 4 Tango Berto Brando 5 Tango Berto Winger 7 Tango Berto Snyder Movie ID Theater Movie 1 Odeon 1 2 Forum 3 3 Forum 2 Schedule
Background image of page 2
3 5 Schema design has its own intricacies This is a bad schema design! Problems Change the name of a theater – Change the name of a movie‟s director What about theaters that play no movie? ID Title Director Actor 1 Wild Lynch Winger 2 Sky Berto Winger 3 Reds Beatty Beatty 4 Tango Berto Brando 5 Tango Berto Winger 7 Tango Berto Snyder ID Theater Movie 1 Odeon 1 2 Forum 3 3 Forum 2 Schedule Movie 6 How to Design a Database and Avoid Bad Decisions With experience… Learn in CSE132A normalization rules of database design a well-developed mathematical theory about how to fix step by step a “bad” schema CSE135: Think entities and relationships then translate to relations Do not confuse the data flow diagrams with the E/R diagrams: they are different formalisms and serve different purposes
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
4 7 Data Structure: Relational Model Example Problem: Represent the students and Spring classes of the CSE department, including the enrollment of students in classes. Students have pid, first name and last name.
Background image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 28

02 - SQL - CSE 135 Database design & SQL programming...

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online