1 / 50

Database

Database . EER. Extended E-R Model. E-R model is sufficient for traditional database applications Nontraditional applications (CAD, multimedia) have more complex requirements Can extend traditional E-R diagrams with semantic data modeling concepts. Inheritance. EER IsA Notation. Phone.

smcclanahan
Download Presentation

Database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database EER

  2. Extended E-R Model • E-R model is sufficient for traditional database applications • Nontraditional applications (CAD, multimedia) have more complex requirements • Can extend traditional E-R diagrams with semantic data modeling concepts

  3. Inheritance

  4. EER IsA Notation Phone PERSON Name ID IsA STUDENT PROFESSOR Major Class Dept Office

  5. Specialization & Generalization • Specialization • process of taking an entity and creating several specialized subclasses Top down design • Generalization • process of taking several related entities and creating a general superclass • Bottom up design • We will talk mainly of specialization, but most information will also apply to generalization

  6. Extended E-R Features: Specialization • Top-down design process; we designate subgroupings within an entity set that are distinctive from other entities in the set. • These subgroupings become lower-level entity sets that have attributes or participate in relationships that do not apply to the higher-level entity set. • Depicted by a triangle component labeled ISA (E.g. customer “is a” person). • Attribute inheritance – a lower-level entity set inherits all the attributes and relationship participation of the higher-level entity set to which it is linked.

  7. Specialization Example

  8. Extended ER Features: Generalization • A bottom-up design process – combine a number of entity sets that share the same features into a higher-level entity set. • Specialization and generalization are simple inversions of each other; they are represented in an E-R diagram in the same way. • The terms specialization and generalization are used interchangeably.

  9. Design Constraints on a Specialization/Generalization • Constraint on which entities can be members of a given lower-level entity set. • condition-defined • Example: all customers over 65 years are members of senior-citizen entity set; senior-citizen ISA person. • user-defined • Constraint on whether or not entities may belong to more than one lower-level entity set within a single generalization. • Disjoint • an entity can belong to only one lower-level entity set • Noted in E-R diagram by writing disjoint next to the ISA triangle • Overlapping • an entity can belong to more than one lower-level entity set

  10. Non-disjoint, Non-covering • Every A can also be a B or a C, or both, or neither A A IsA o B C B C

  11. Disjoint, Non-covering • Every A can also be a B or a C or but not both A A IsA disjoint d B C B C

  12. Position Student # Name Rank S.I.N. IS-A Relationship (cont’d) Employee d Staff Teaching Assistant Faculty

  13. Disjointness constraint • Specifies that an entity can be a member of at most one subclass • There can be no overlap between the subclasses • We use the notation of a d in a circle to symbolize that the subclasses are disjoint

  14. Pension Plan Type Position Rank S.I.N. Predicate-defined subclass Employee Note: not all employees included d Staff Faculty

  15. Jobtype Rank S.I.N. Rank Year Attribute-defined subclass Employee Jobtype d “Faculty” “Staff” “Student” Students Faculty Staff

  16. Overlap • Entities are able to belong to more than one subclass • Notation is an o inside of a circle

  17. Disjoint Constraints Disjoint constraint when an entity can be a member of only one of the subclasses of the specialization. STAFF d FULL-TIME STAFF PART-TIME STAFF Hourly-rate salary

  18. Jobtype Rank S.I.N. Rank Year Overlap Employee A staff member may also be a student o Faculty Staff Students

  19. Specialization constraints • Specializations can be predicate-defined or attribute-defined (otherwise called user-defined) • Disjointness constraint – specialization is disjoint or overlapping • Completeness constraint – specialization is total or partial

  20. DesignConstraintson a Specialization/Generalization (Cont.) • Completenessconstraint -- specifies whether or not an entity in the higher-level entity set must belong to at least one of the lower-level entity sets within a generalization. • total: an entity must belong to one of the lower-level entity sets • partial: an entity need not belong to one of the lower-level entity sets

  21. Completeness Constraint • May be total or partial • for total, every entity in the superclass must belong to a subclass • for partial, entities in the superclass do not need to be part of any subclass • notation for total and partial are the same as in a regular E-R diagram – single and double lines

  22. Participation Constraints Mandatory (total) participation where every member in the superclass must also be a member of a subclass. STAFF FULL-TIME STAFF PART-TIME STAFF Hourly-rate salary

  23. Participation Constraints Optional (partial) participation where a member in the superclass need not belong to any of its subclasses. STAFF MANAGER SECRETARY SALES PERSONNEL

  24. Jobtype Rank S.I.N. Rank Year Partial Employee o Faculty Staff Students

  25. Jobtype Rank S.I.N. Rank Year Total Employee o Staff Students Faculty

  26. Hierarchies and Lattices • Hierarchies • a tree-like structure where each subclass belongs to only one superclass • everything we have seen so far is a hierarchy • Lattices • a graph-like structure where a subclass can belong to more than one superclass

  27. course salary name student # Lattice Person o Employee Student Teaching Assistant

  28. EXample

  29. Example

  30. Aggregation • Represents a “part-of” relationship between entity types, where one represents the ‘whole’ and the other the ‘part’. • No inherited attributes; each entity has its own unique set of attributes.

  31. Aggregation • Consider the ternary relationship works_on, which we saw earlier • Suppose we want to record managers for tasks performed by an employee at a branch

  32. Aggregation (Cont.) • Relationship sets works_on and manages represent overlapping information • Every manages relationship corresponds to a works_on relationship • However, some works_on relationships may not correspond to any manages relationships • So we can’t discard the works_on relationship

  33. Aggregation (Cont.) • Without introducing redundancy, the following diagram represents: • An employee works on a particular job at a particular branch • An employee, branch, job combination may have an associated manager

  34. E-R Diagram With Aggregation

  35. Aggregation TEAM MEMBER

  36. ER Notation

  37. Summary of Symbols (Cont.)

  38. E-R Diagram for a Banking Enterprise

  39. Case Study • Design a DB consistent with the following facts. • Trains are either local trains or express trains, but never both. • A train has a unique number and an engineer. • Stations are either express stops or local stops, but never both. • A station has a unique name and an address. • All local trains stop at all stations. • Express trains stop only at express stations. • For each train and each station the train stops at, there is a time.

  40. Design 1: bad number type name time addr StopsAt trains stations engineer type Problem: does not capture the constraints that express trains only stop only at express stations and local trains stop at all local stations

  41. Design 2: good number engineer train name time address ISA StopsAt2 stations local trains ISA express trains time StopsAt1 local stations express stations

  42. Exercise • A company database needs to store information about employees (identified by ssn, with salary and phone as attributes), departments (identified by dno, with dname and budget as attributes), and children of employees (with name and age as attributes). Employees work in departments; each department is managed by an employee; a child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known.

  43. Teaching Database Design an E-R schema for a database to store info about professors, courses and course sections indicating the following: • The name and employee ID number of each professor • The salary and email address(es) for each professor • How long each professor has been at the university • The course sections each professor teaches • The name, number and topic for each course offered • The section and room number for each course section • Each course section must have only one professor • Each course can have multiple sections

  44. A Case Study Conference centre booking system A conference centre takes bookings from clients who wish to hold courses or conferences at the centre. When clients make bookings they specify how many people are included in the booking, and of these, how many will be resident during the booking, and how many will require catered or non-catered accommodation at the centre. The centre contains a number of facilities which may be required by clients making bookings as follows: A. There are 400 bedrooms for clients who will be resident during the Course or conference. B. A maximum of 250 catered people can be handled at any one time. C. Six main lecture theatres providing seating for 200 people. D. Twenty seminar rooms each able to accommodate 25 people. E. Video conference facilities. The video conference facilities consist of four separate video conference networks. Each video conference network has a large screen based in one of the main lecture theatres, along with 3 satellite screens each of which is based in one of the seminar rooms.

  45. University DB Case Study • Maintain the following information about undergrad students: • Name, address, student number, date of birth, year of study, degree program (BA, BSc, BCS), concentration (Major, Honours, etc) and department of concentration. • Note: An address is composed of a street, city, province and postal code; the student number is unique for each student

  46. University Case Study (cont’d) • Maintain information about departments • Name, code (CS, Phy), office phone, and faculty members • Maintain information about courses: • Course number (3753), title, description, prerequisites. • Maintain information about course sections: • Section (A, B, C), term (X1), slot #, instructor

  47. University Case Study (cont’d) • Maintain information about faculty: • Name, rank, employee number, salary, office number, phone number and email address. • Note: employee number is unique • Maintain a program of study for the current year for each student: • i.e. courses that each student is enrolled in

More Related