1Relational Database DesignPart ICPS 116Introduction to Database Systems2Announcements (Tue. Sep. 2) cps116.cod accounts created; change your password! Let me know if you have NOT received the email Homework #1 is out Due in two weeks Readings: see Tentative Syllabus on course website Also posted chapters in the first edition Still negotiating a new room Make ensure you check email on Wednesday evening!3Relational 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)24Keys 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 diiNo proper subset of Ksatisfies the above condition•That is, K is minimalExample: 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 Key declarations are part of the schema456Ralph82.36More examples of keys Enroll (SID, CID) Address (street_address, city, state, zip)_37Usage 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 lA 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 diagrams410E/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 rectangleRel tionshipn ssoci tion mong entitiesRelationship: 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 CoursesCIDtitleEnrollSIDname Where do the grades go? With Students? With Courses?513More 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’s616N-ary versus binary relationships Can we model n-ary relationships using just binary relationships?Students Enroll CoursesTA’sAssignedTo Assist17Roles 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 diamonds719Weak entity set examples Seats in rooms in buildingsRooms In BuildingsnameyearnumbercapacityIInSeatsnumberL/R? Why must double diamonds be many-one/one-one?20Modeling n-ary relationships An n-ary relationship set can be replaced by a weak entity set (called a connecting entity set) and nbinary relationship setsStudents Enroll CoursesTA’sNote that the multiplicityconstraint is lostTA’sEnrollStudents Courses21ISA relationships Similar to the idea of subclasses in object-oriented programming: subclass = special case, fewer entities, and possibly more properties Represented as a triangle (direction is important) Example: Graduate students are students, but they also have officesStudents
View Full Document
Unlocking...