1Relational Database DesignPart IICPS 116Introduction to Database Systems2Announcements (September 6) Homework #1 due in 1½ weeks Start early!!! Details of the course project and a list of suggested ideas will be available next Tuesday3Database design steps: review Understand the real-world domain being modeled Specify it using a database design model (e.g., E/R)Translate specification to the data model of DBMSTranslate specification to the data model of DBMS (e.g., relational) Create DBMS schema) Next: translating E/R design to relational schema24E/R model: review Entity sets Keys Weak entity sets Relationship setsAib li hiAttributes on relationships Multiplicity Roles Binary versus N-ary relationships•Modeling N-ary relationships with weak entity sets and binary relationships ISA relationships5Translating entity sets An entity set translates directly to a table Attributes → columns Key attributes → key columnsStudents CoursesCIDtitleEnrollSIDnamegradeStudent (SID, name) Course (CID, title)6Translating weak entity sets Remember the “borrowed” key attributes Watch out for attribute name conflictsRooms In BuildingsnameyearnumbercapacityBuilding (building_name, year)Rooms (building_name, room_number, capacity)Seats (building_name, room_number, seat_number, left_or_right)yearcapacityInSeatsnumberL/R?37Translating relationship sets A relationship set translates to a table Keys of connected entity sets → columns Attributes of the relationship set (if any) → columns Multiplicity of the relationship set determines the key of the tableStudents CoursesCIDtitleEnrollSIDnamegradeEnroll (SID, CID, grade)8More examplesEnrollStudents CoursesTA’sCIDtitleSIDnameTIDnameMarry (husband_SSN, wife_SSN)Persons MarryhusbandwifeSSN9Translating double diamonds Recall that a double-diamond relationship set connects a weak entity set to another entity set No need to translate because the relationship is implicit in the weak entity set’s translationnamenumberRooms In BuildingsnameyearnumbercapacityInSeatsnumberL/R?RoomInBuilding(room_building_name, room_number,building_name)is subsumed byRooms (building_name, room_number, capacity)410Translating subclasses & ISA (approach 1) Entity-in-all-superclasses approach (“E/R style”) An entity is represented in the table for each subclass to which it belongs A table includes only the attributes directly attached to the corresponding entity set, plus the inherited keyStudents CoursesCIDtitleEnrollSIDnameoffice GradStudentsISACourse (CID, title)Student (SID, name)Enroll (SID, CID)GradStudent (SID, office)h 444, “Apu”i∈h 142, “Bart”ih 444, “D444”i ∈11Translating subclasses & ISA (approach 2) Entity-in-most-specific-class approach (“OO style”) An entity is only represented in one table (corresponding to the most specific entity set to which the entity belongs) A table includes the attributes attached to the corresponding entity set, plus all inherited attributesStudents CoursesCIDtitleEnrollSIDnameoffice GradStudentsISACourse (CID, title)Student (SID, name)Enroll (SID, CID)GradStudent (SID, name, office)h 444, “Apu”, “D444”i ∈h 142, “Bart”i ∈12Translating subclasses & ISA (approach 3) All-entities-in-one-table approach (“NULL style”) One relation for the root entity set, with all attributes found anywhere in the network of subclasses Use a special NULL value in columns that are not relevant for a particular entityStudents CoursesCIDtitleEnrollSIDnameoffice GradStudentsISACourse (CID, title)Student (SID, name, office)Enroll (SID, CID)h 444, “Apu”, “D444”i∈h 142, “Bart”, NULLi513Comparison of three approaches Entity-in-all-superclasses Student (SID, name), GradStudent (SID, office) Pro: Con: Entity-in-most-specific-class Student (SID, name), GradStudent (SID, name, office) Pro: Con: All-entities-in-one-table Student (SID, name, office) Pro: Con:14A complete exampleTr a i n s StationsnameaddressnumberengineertimeLocalTrainsLocalStationsISALocalTrainStopsISAExpressTrains ExpressStationstimeExpressTrainStopsTr a i n (number, engineer)LocalTrain (number)ExpressTrain (number)Station (name, address)LocalStation (name)ExpressStation (name)LocalTrainStop (local_train_number, station_name, time)ExpressTrainStop (express_train_number, express_station_name, time)15Simplifications and refinementsTrain (number, engineer), LocalTrain (number), ExpressTrain (number)Station (name, address), LocalStation (name), ExpressStation (name)LocalTrainStop (local_train_number, station_name, time)ExpressTrainStop (express_train_number, express_station_name, time)616An alternative designTrain (number, engineer, type)Station (name, address, type)TrainStop (train_number, station_name, time) Encode the type of train/station as a column rather than creating subclassesg Some constraints are no longer captured Type must be either “local” or “express” Express trains only stop at express stations)Fortunately, they can be expressed/declared explicitly as database constraints in SQL) Arguably a better design because it is simpler!17Design principles KISS Keep It Simple, Stupid Avoid redundancy Redundancy wastes space, complicates updates and deletes, promotes inconsistency Capture essential constraints, but don’t introduce unnecessary restrictions Use your common sense Warning: mechanical translation procedures given in this lecture are no substitute for your own
View Full Document
Unlocking...