DOC PREVIEW
Duke CPS 116 - Relational Database Design

This preview shows page 1-2 out of 5 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 5 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 5 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 5 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

1Relational Database DesignPart ICPS 116Introduction to Database Systems2Announcements (September 4) rack040 accounts created; change your password! Let me know if you have NOT received the email Homework #1 is out (earlier than scheduled) Due in two weeks Reading: see Tentative Syllabus on course website3Relational model: review A database is a collection of relations (or tables) Each relation has a list of attributes (or columns)Each attribute has adomain(ortype)Each attribute has a domain(or type) Each relation contains a set of tuples (or rows)4Keys 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”NbfKifi h b diiNo proper subset of Ksatisfies 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)5Schema vs. dataStudentSID name age GPA142 Bart 10 2.3123 Milhouse 10 3.1857 Lisa 8 4.3456Ralph823 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 schema456Ralph82.36More 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)27Usage 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”8Database 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 DBMSAf lA few popular ones:• Entity/Relationship (E/R) model• Object Definition Language (ODL)• UML (Unified Modeling Language)Translate specification to the data model of DBMS Relational, XML, object-oriented, etc. Create DBMS schema9Entity-relationship (E/R) model Historically and still very popular Can think of as a “watered-down” object-oriented design model Primarily a design model—not directly ygyimplemented by DBMS Designs represented by E/R diagrams We use the style of E/R diagram covered by GMUW; there are other styles/extensions Very similar to UML diagrams10E/R basics Entity: a “thing,” like an object Entity set: a collection of things of the same type, like a relation of tuples or a class of objects Represented as a rectangleRel tionshipn ssoci tion mong entitiesRelationship: an association among entities Relationship set: a set of relationships of the same type (among same entity sets) Represented as a diamond Attributes: properties of entities or relationships, like attributes of tuples or objects Represented as ovals11An example E/R diagram Students enroll in coursesStudents CoursesCIDtitleEnrollSIDname A key of an entity set is represented by underlining all attributes in the key A key is a set of attributes whose values can belong to at most one entity in an entity set—like a key of a relation12Attributes of relationships Example: students take courses and receive gradesStudents CoursesCIDtitleEnrollSIDnamegradegrade Where do the grades go? With Students?• But a student can have different grades for multiple courses With Courses?• But a course can assign different grades for multiple students With Enroll!313More on relationships There could be multiple relationship sets between the same entity sets Example: Students Enroll Courses; Students TA Courses In a relationship set, each relationship is uniquely identified by the entities it connects Example: Between Bart and CPS116, there can be at most one Enroll relationship and at most one TArelationship)What if Bart took CPS116 twice and got two different grades?14Multiplicity of relationships E and F: entity sets Many-many: Each entity in E is related to 0 or more entities in F and vice versa Example: Many-one: Each entity in E is related to 0 or 1 entity in F, Students Enroll Coursesyyybut each entity in F is related to 0 or more in E Example: One-one: Each entity in E is related to 0 or 1 entity in Fand vice versa Example: “One” (0 or 1) is represented by an arrow “Exactly one” is represented by a rounded arrowCourses TaughtBy InstructorsStudents Own AcpubAccounts15N-ary relationships Example: Each course has multiple TA’s; each student is assigned to one TAStudents Enroll Courses Meaning of an arrow into E: Pick one entity from each of the other entity sets; together they must be related to either 0 or 1 entity in ETA’s16N-ary versus binary relationships Can we model n-ary relationships using just binary relationships?Students Enroll CoursesTA’sAssignedTo Assist No; for example: Bart takes CPS116 and CPS114 Lisa TA’s CPS116 and CPS114 Bart is assigned to Lisa in CPS116, but not in CPS11417Roles in relationships An entity set may participate more than once in a relationship set) May need to label edges to distinguish roles Examplesp People are married as husband and wife; label needed People are roommates of each other; label not neededPersons MarryhusbandwifeRoommate18Weak entity sets Sometimes, the key of an entity set Ecomes not completely from its own attributes, but from the keys of other (one or more) entity sets to which E is linked by many-one (or one-one) li hirelationship sets Example: Rooms inside Buildings are partly identified by Buildings’ name E is called a weak entity set• Denoted by double rectangle• The relationship sets through which E obtains its key are drawn as double diamonds419Weak entity set examples Seats in rooms in buildingsRooms In BuildingsnameyearnumbercapacityIInSeatsnumberL/R? Why must double diamonds be many-one/one-one? With many-many, we would not know which entity provides


View Full Document

Duke CPS 116 - Relational Database Design

Documents in this Course
Part I

Part I

8 pages

XSLT

XSLT

4 pages

XSLT

XSLT

8 pages

Part I

Part I

8 pages

XSLT

XSLT

8 pages

Load more
Download Relational Database Design
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Relational Database Design and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Relational Database Design 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?