High-Level Database Models Spring 2011 Instructor: Hassan Khosravi - PowerPoint PPT Presentation

High-Level Database Models Spring 2011 Instructor: Hassan Khosravi

Database Modeling and implemnation process High-Level Relational Database Relational Ideas Design Schema DBMS 4.2

The Entity/Relationship Model  The structure of data is represented graphically using  Entity sets  An abstract object of some sort  Attributes  properties of the entities  Primitive type : String, integer, real  Relationships  Connections among entities. 4.3

Entity/Relationship Diagram  Entity sets are represented by rectangles  Attributes are represented by ovals  Relationships are represented by diamonds year length type title name Stars-in Movies Star address Owns Studios address name 4.4

Multiplicity of Binary E/R Relationships  In general, a binary relationship can connect any member of one of its entity sets to any number of members of the other entity set. year length type title name Stars-in Movies Star address  Suppose R is a relation connecting entity sets E and F  If each member of E can be connected by R to at most one member of F, then we say R is many-one from E to F e1 f e2 e3  If each member of F can be connected by R to at most one member of E, then we say R is many-one from F to E, or one- many from E to F 4.5

 Example of a many-one relationship from Movie to studio year length type title Movies Owns Studios address name 4.6

 If R is both many-one from E to F and many-one from F to E then we say that R is one to one Studios Runs Presidents 4.7

Multiway Relationships  E/R model makes it convenient to define relationships involving more than two entity sets. Movies Contracts Stars Studios  An arrow pointing to an entity E means that if we select one entity from each of the other entity sets, those entities are related to at most one entity in E. 4.8

Limitations on Arrow notation  Not enough choice of arrow to determine every situation  Movie determines studio?  stars determine studio?  Movie + star determine studio? Movies Contracts Stars Studios 4.9

Roles in Relationships  It is possible that one entity set appears two or more times in a single relationship. If so, we draw as many lines from the relationship to the entity set as the entity set appears in the relationship. Contracts(starname, title, year, studioOfstar, producingStudio)  One studio having a certain star under contract (in general) , one for a specific film. 4.10

Roles in Relationships  What do the arrows mean?  Given a star, a movie, and a producing studio, the studio of the star is unique  Given a star, a movie, and a studio for star, the producing studio is unique 4.11

Attributes on relationships  Sometimes it is convenient or even essential to associate attributes with a relationship. salary year title name Movies Stars Contracts length address type Studios name address  Salary can not be part of stars table as they might get different salary for different movies.  Salary cannot be part of Movies as different stars getting different salaries. 4.12

Attributes on relationships  It is never necessary to place attributes on relationships. We can instead invent a new entity set salary Salary year title name Movies Stars Contracts length address type Studios name address 4.13

Converting Multiway relationships to Binary  E/R model does not require binary relationships, but other models do  UML(4.7) and ODL(4.9) limit relationships to be binary  It is generally useful to observe that any relationship connecting more than two entity sets can be converted to a collection of binary relations. 4.14

Subclasses in the E/R Model  An entity set may contain certain entities that have special properties not associated with all members of the set.  We can use a “ isa ” relationship which is presented by a triangle  Cartoons have voice of stars  Murder mysteries have weapon  In general entity sets connected by “ isa ” relationship could have any structure. We shall limit it to trees 4.15

Subclasses in the E/R Model  Typical movies being neither will have 4 attributes  A cartoon movie would have 4 attributes and voice relationship  A murder mystery would have 5 attributes  A movie like Roger Rabbit which is both a cartoon and a murder mystery will have 5 attributes and voice relationship 4.16

Design Principles  Faithfulness  Avoiding redundancy  Simplicity  Right relationships  Right elements 4.17

Faithfulness  The design must be faithful to the specification of the application. It should reflect reality.  The stars-in relation between stars and movies must be many to many as observed in real world  Sometimes it is less obvious  Instructors, courses and a relation teaches between them. Is the relation many-many? Many-one? – The answer relies on the schools policy that a few instructors could teach the same course or not. 4.18

Avoiding Redundancy  We should be careful to say everything once.  Redundancy: Unnecessarily repeated info in several tuples  Star Wars, 1977, 124, SciFi, and Fox is repeated.  Update Anomaly: Changing information in one tuple but leaving the same info unchanged in another  If you find out that Star Wars is 125 minute and you don’t update all of them, you will lose the integrity.  Deletion Anomaly: Deleting some info and losing other info as a side effect Title Year Length Genre StudioName StarName Star Wars 1977 124 SciFi Fox Carrie Fisher Star Wars 1977 124 SciFi Fox Mark Hamill Star Wars 1977 124 SciFi Fox Harrison Ford Gone with the wind 1939 231 Drama MGM Vivien Leigh Wayne’s World 1992 95 Comedy Paramount Dana Carvey Wayne’s World 1992 95 Comedy Paramount Mike Meyers 4.19

Simplicity  Avoid introducing more elements into your design than is absolutely necessary. We need to make the data as abstract as possible  Existence of movie-holdings which shows the ownership of a single movie.  This structure is closer to reality, however it holds no useful info 4.20

Right Relationships Stars-in Movies Star Stars Movies Contracts Owns Studios Studios  We omitted the owns and the stars-in relationships when we introduced contract was that a right decision?  We don’t know. It depends on our assumptions  It might be possible to deduce the relationship stars-in from contract. If a star can appear in a movie only with a contract. – However there may be no contract – They may be no recorded contract  If for every movie there is at least one contract involving the movie, the studio and some stars then we can eliminate owns  If a studio can own a movie and yet there are still no stars then we can not eliminate owns 4.21

Right Relationships  We can use the two relationships stars-in and owns to conclude that a star could work for a studio.  Is it rational to add such a relationship?  Depends, if it doesn’t add any new info basically means that star working for a movie owned by the studio then no  If its possible to work for a studio without being on the movie then yes 4.22

Right Elements  were we wise to make studio an entity instead of adding it to the movie table year  Redundancy in address length type title  What if there was no address for studio? Stars-in Movies Then it would have been  reasonable. Star Owns name Studios address address name 4.23

Right Elements  Conditions under which we prefer to use an attribute instead of an entity set  Suppose E is an entity set  E must be the “one” in many -one relationships  If the movie can have more than studio it wouldn’t make sense to have an attribute for it  The only key for E is all its attributes  Address was dependent on name and that was stopping us from using studio as a attribute  No relationship involves E more than once 4.24

Constraints in the E/R Model  Keys in the E/R model  Referential integrity  Degree Constraints 4.25

Keys in the E/R Model  Every entity set must have a key  In some cases isa and weak entity sets have keys that belong to other tables  There can be more than one key, we pick one to be the primary key.  In isa relationships we require the root to have all the attributes needed for a key.  We underline the attributes belonging to a key for an entity set. year length type title name Stars-in Movies Star address 4.26

Referential Integrity  Many-one requirements simply says that no movie can be owned by two studios. It doesn’t say that a movie must be owned by a studio.  The owns relationship has a referential integrity constraint  There must be one owning studio.  The studio must be listed in the studio tables. Movies Owns Studios Runs Presidents  Suppose R is a relationship from E to F  A rounded arrow-head pointing to F indicates not only that the relationship is many-one from E to F, but that the entity of set F related to a given entity of set E is required to exist 4.27