{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

03-design - Announcements(Thu Sep 8 Relational Database...

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

View Full Document Right Arrow Icon
1 Relational Database Design Part I CPS 116 Introduction to Database Systems 2 Announcements (Thu. Sep. 8) Homework #1 is out Due in 12 days, but start early—as soon as any portion has been covered Sign up for Gradiance Try our VM now We can’t help you on the due date if you run into last- minute installation problems Duke CS account created Email me immediately if you still need one Readings: see Tentative Syllabus on course website 3 Relational model: review A database is a collection of relations (or tables) Each relation has a list of attributes (or columns) Each attribute has a domain (or type) Each relation contains a set of tuples (or rows) 4 Keys A set of attributes K is a key for a relation R if In no instance of R will two different tuples agree on all attributes of K • That is, K is a “tuple identifier” No proper subset of K satisfies the above condition • That is, K is minimal Example: Student ( SID , name , age , GPA ) SID is a key of Student age is not a key (not an identifier) { SID , name } is not a key (not minimal) 5 Schema vs. instance Is name a key of Student ? Yes? Seems reasonable for this instance No! Student names are not unique in general Key declarations are part of the schema Student SID name age GPA 142 Bart 10 2.3 123 Milhouse 10 3.1 857 Lisa 8 4.3 456 Ralph 8 2.3 6 More examples of keys Enroll ( SID , CID ) { SID , CID } ) A key can contain multiple attributes! Address ( street_address , city , state , zip ) { street_address , city , state } { street_address , zip } ) A relation can have multiple keys! • We typically pick one as the “primary” key, and underline all its attributes, e.g., Address ( street_address , city , state , zip )
Background image of page 1

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

View Full Document Right Arrow Icon
2 7 Usage of keys More constraints on data, fewer mistakes Look up a row by its key value Many selection conditions are “key = value” “Pointers” Example: Enroll ( SID , CID ) SID is a key of Student CID is a key of Course • An Enroll tuple “links” a Student tuple with a Course tuple Many join conditions are “key = key value stored in another table” 8 Database design Understand the real-world domain being modeled Specify it using a database design model More intuitive and convenient for schema design But not necessarily implemented by DBMS A few popular ones: • Entity/Relationship (E/R) model • Object Definition Language (ODL)
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}