Скачать презентацию Database Design Conceptual Modeling with ER Model CENG Скачать презентацию Database Design Conceptual Modeling with ER Model CENG

5d96a26aea29256338e447c61251ca08.ppt

  • Количество слайдов: 55

Database Design: Conceptual Modeling with ER Model CENG 351 Database Design: Conceptual Modeling with ER Model CENG 351

Database Design Process • Step 1: Conceptual database design – High level description of Database Design Process • Step 1: Conceptual database design – High level description of the data and the constraint – This step can use ER or similar high level models since name ssn dname lot Employees budget did Works_In Departments • Step 2: Logical database design – Convert database design into a database schema CREATE TABLE Employees (ssn CHAR(11), name CHAR(20), lot INTEGER, PRIMARY KEY (ssn)) CENG 351

Database Design Process (cont. ) • Step 3: Schema refinement • Step 4: Physical Database Design Process (cont. ) • Step 3: Schema refinement • Step 4: Physical database design – Data storage, indexing • Step 5: Access Control – Identify which users can access which par of the data CENG 351

ER Model Basics: Entity set ssn name lot Employees • Entity: Real-world object distinguishable ER Model Basics: Entity set ssn name lot Employees • Entity: Real-world object distinguishable from other objects. – An entity is described (in DB) using a set of attributes. • Entity Set: A collection of similar entities. E. g. , all employees. – – All entities in an entity set have the same set of attributes. Each entity set has a key that uniquely identifies it. Each attribute has a domain. Entity set can be mapped to a relation easily. CENG 351

ER Model Basics: Relationship set lot Employees ssn since name ssn name dname Employees ER Model Basics: Relationship set lot Employees ssn since name ssn name dname Employees budget did Works_In lot Departments supervisor subordinate Reports_To • Relationship: Association among 2 or more entities. E. g. , Steve Jobs works in Grocery department. • Relationship Set: Collection of similar relationships. • Same entity set could participate in different relationship sets, or in different “roles” in the same set. CENG 351

ER Model Basics: Relationship Set • Relationship sets can also have descriptive attributes (e. ER Model Basics: Relationship Set • Relationship sets can also have descriptive attributes (e. g. , the since attribute of Works_In). For a given entity pair we cannot have more than one associated descriptive attribute value. E. g. , an employee-department pair can have one since attribute value. • A relationship must be uniquely identified by the entities taking part. • Thus, in translating a relationship set to a relation, attributes of the relation must include: – Keys for each participating entity set (as foreign keys): This set of attributes (individual keys) forms superkey for the new relation. – All descriptive attributes of the relationship. CENG 351

ER Model Basics: Relationship Set • An instance of a relationship set is a ER Model Basics: Relationship Set • An instance of a relationship set is a set of relationships • An example instance may have an employee working in none, one or many departments. • Ternary Relationship: A relationship having three entities involved. – E. g. , Works_In can have a Locations entity with possible attributes. taking part as well, where a department may have offices in different cites. CENG 351

Additional features • One would need additional features for the ER diagrams to represent Additional features • One would need additional features for the ER diagrams to represent some important properties of entities and their relationships with other entities. • Important features that can be shown on the ER diagrams: – – – Key constraints Participation constraint Weak entity Class hierarchy concept Aggregation concept CENG 351

Key Constraints • Many-to-many Mapping: Consider Works_In relationship : An employee can work in Key Constraints • Many-to-many Mapping: Consider Works_In relationship : An employee can work in many departments; a dept can have many employees • One-to-many Mapping: Consider a Manages relationship between entities Employee and Departments. Each dept has at most one manager and each employee can manage more than one department (key constraint on Manages) • The Key constraint is represented by an arrow as in the related diagram. CENG 351

Key Constraints Departments have single manager, employee can manage more than one department! since Key Constraints Departments have single manager, employee can manage more than one department! since name ssn dname lot Employees did Manages CENG 351 budget Departments

Types of relationship sets 1 -to Many-to-1 1 -to-1 Many-to-Many (one-to-one) (one-to-many) (Many-to-one) CENG Types of relationship sets 1 -to Many-to-1 1 -to-1 Many-to-Many (one-to-one) (one-to-many) (Many-to-one) CENG 351

Participation Constraints: Total vs. Partial • If every department has a manager – then, Participation Constraints: Total vs. Partial • If every department has a manager – then, this is a participation constraint: • the participation of Departments in Manages is said to be total (vs. partial). • For total participation implementation in relations, every did value in the corresponding Departments table must appear in a row of the Manages table, with a non-null ssn value, reflecting the situation that every department has a manager. • shown with bold line CENG 351

Participation constraints (cont. ) • Diagrammatic representation of key constraint and total participation since Participation constraints (cont. ) • Diagrammatic representation of key constraint and total participation since name ssn did lot Employees dname Manages CENG 351 budget Departments

Participation Constraints (cont. ) • Assume the case where each employee works in at Participation Constraints (cont. ) • Assume the case where each employee works in at least one department and that each department has at least one employee. • The example ER diagram shows Manages and Works_In in the same diagram, with correct properties. since name ssn did lot Employees dname Manages Works_In since CENG 351 budget Departments

Participation Constraints (cont. ) • Ensure total participation in the corresponding relationship tables is Participation Constraints (cont. ) • Ensure total participation in the corresponding relationship tables is a problem! • We have to guarantee that every did value in Departments appears in a tuple of Works_In at least once or possibly more… CENG 351

The (min, max) notation (0, 1) (1, 1) Chapter 3 -16 (1, 1) (1, The (min, max) notation (0, 1) (1, 1) Chapter 3 -16 (1, 1) (1, N)

Weak Entities • Some entities do not have to have a key. It is Weak Entities • Some entities do not have to have a key. It is possible that some entities are dependent on other entities. That is their existence depends on the others… This dependent entity is known as weak entity. • A weak entity can be identified uniquely only by considering the primary key of another (owner) entity. • Owner entity set and weak entity set must participate in a one-tomany relationship set: one owner many weak entities • Weak entity set must have total participation in this identifying relationship set. • A weak entity always has a partial key, it can only be uniquely defined if we take its key together with the key of the owner entity. CENG 351

Weak Entities (cont. ) • Assume an example of Employee and Dependents entities with Weak Entities (cont. ) • Assume an example of Employee and Dependents entities with a Policy relationship between tham. • Employee is owner entity, Dependent is weak entity, Policy is the identifying relationship set: name ssn lot Employees cost Policy CENG 351 pname age Dependents

ISA (`is a’) Hierarchies v v Some time it is more appropriate to represent ISA (`is a’) Hierarchies v v Some time it is more appropriate to represent entities in an entity set into subclasses, where attributes can be inherited down the hierarchy. As in object oriented programming tools, attributes are inherited. v If we declare A ISA B, every A entity is also considered to be a B entity. v As an example, consider a Employee entity as a super-class which has two subclasses as hourly and contract entities. In this case, the attributes of Employee should be inherited by the hourly and contract employee entities. CENG 351

name ISA (`is a’) Hierarchies ssn lot Employees Subclasses are defined first! hourly_wages hours_worked name ISA (`is a’) Hierarchies ssn lot Employees Subclasses are defined first! hourly_wages hours_worked ISA Hourly_Emps contractid Contract_Emps • Overlap constraints: Can Zeynep be an Hourly_Emps as well as a Contract_Emps entity? (posibility this is not to be allowed to be overlapped. However if had a sub -class such as Senior employee entitiy, that can be have OVERLAP with the subclass Contract and or Hourly entities. ) • Covering constraints: Does every Employees entity also have to be an Hourly_Emps or a Contract_Emps entity? (allowed using COVER). In some cases this may be required, in some other cases may not. • These two issues need to be represented by the programming tool used to implement such properties. CENG 351

ISA (`is a’) Hierarchies • Reasons for using ISA: – To add descriptive attributes ISA (`is a’) Hierarchies • Reasons for using ISA: – To add descriptive attributes specific to a subclass. – To identify the set of entities that participate in a particular relationship, e. g. only Senior Employees can be allowed to be Managers of Departments. CENG 351

Translating ISA Hierarchies to Relations • General approach: – 3 relations: Employees, Hourly_Emps and Translating ISA Hierarchies to Relations • General approach: – 3 relations: Employees, Hourly_Emps and Contract_Emps. • Hourly_Emps: Every employee is recorded in Employees. For hourly emps, extra info recorded in Hourly_Emps (hourly_wages, hours_worked, ssn); must delete Hourly_Emps tuple if referenced Employees tuple is deleted). • Queries involving all employees are easy, those involving just Hourly_Emps require a join to get some attributes. • Overlap and covering constraints can only be expressed using assertions in case of SQL. CENG 351

Aggregation • Building a relationship between a collection of entities and relationships • Aggregation Aggregation • Building a relationship between a collection of entities and relationships • Aggregation allows us to indicate a relationship set participate in another relationship set CENG 351

ssn Aggregation Suppose: • entity set Projects • each Projects is sponsored by at ssn Aggregation Suppose: • entity set Projects • each Projects is sponsored by at least one Departments • each Departments that sponsors a Projects might assign employees to monitor sponsorship Intuitively… ØMonitors should be a relationship set that associates a Sponsors relation (versus a Projects or Departments) with an Employees entity. name lot Employees since started_on pid pbudget Projects CENG 351 did Sponsors dname budget Departments

ssn Aggregation • Used when we have to model a relationship involving (entity sets ssn Aggregation • Used when we have to model a relationship involving (entity sets and) a relationship set. • Aggregation allows us to treat a relationship set as an entity set for purposes of participation in (other) relationships. name lot Employees Monitors since started_on pid pbudget Projects CENG 351 until did Sponsors dname budget Departments

Aggregation vs. Ternary relation ssn name lot Employees Each relationship in an aggregation may Aggregation vs. Ternary relation ssn name lot Employees Each relationship in an aggregation may have a different descriptive attribute like since and until. Suppose: • No need to record until attribute of Monitors one can use ternary relationship Sponsors 2 ØThen… since started_on pid pbudget Projects did Sponsors 2 dname budget Departments But suppose we have constraint that: Each sponsorship (of a project by a department) be monitored by at most one employee? Then… one can’t do it with Sponsors 2 Need aggregated relationship Sponsors CENG 351

Aggregation vs. Ternary relation (Contd. ) ssn name lot Employees Monitors is a distinct Aggregation vs. Ternary relation (Contd. ) ssn name lot Employees Monitors is a distinct relationship, with a descriptive attribute (until). until Ø since started_on pid pbudget Projects Also, can say that each sponsorship is monitored by at most one employee. Ø CENG 351 did Sponsors dname budget Departments

Conceptual Design Using the ER Model • Design choices: – Should a concept be Conceptual Design Using the ER Model • Design choices: – Should a concept be modeled as an entity or an attribute? – Should a concept be modeled as an entity or a relationship? – Identifying relationships: Binary or ternary? Aggregation? • Constraints in the ER Model: – A lot of data semantics can (and should) be captured. – But some constraints cannot be captured in ER diagrams. • Need for further refinement of the schema: – Relational schema obtained from ER diagram is a good first step. But ER design is subjective & can’t express certain constraints; so this relational schema may need refinement. CENG 351

Entity vs. Attribute • Should address be an attribute of Employees or an entity Entity vs. Attribute • Should address be an attribute of Employees or an entity (connected to Employees by a relationship)? • Depending upon the purpose of address information and the semantics of the data: • If we have several addresses per employee, address must be an entity (since attributes cannot be set-valued). • If the structure (city, street, etc. ) is important, e. g. , we want to retrieve employees in a given city, address must be modeled as an entity (since attribute values are atomic). CENG 351

Entity vs. Attribute (Contd. ) • According to the conceptual ER model, Works_In 2 Entity vs. Attribute (Contd. ) • According to the conceptual ER model, Works_In 2 does not allow an employee to work in a department for two or more periods. from name ssn Employees v to lot did dname budget Departments Works_In 2 But we want several values of the descriptive attributes for each instance of this relationship. CENG 351

Entity vs. Attribute (Contd. ) • In this case, use Duration entity to allow Entity vs. Attribute (Contd. ) • In this case, use Duration entity to allow more than one duration per relationship… ssn name lot Employees from did Works_In 3 Duration CENG 351 dname budget Departments to

Entity vs. Relationship • Following ER diagram OK if a manager gets a separate Entity vs. Relationship • Following ER diagram OK if a manager gets a separate discretionary budget for each dept. since dbudget name ssn dname lot Employees did Manages 2 CENG 351 budget Departments

Entity vs. Relationship • What if a manager gets a discretionary budget which is Entity vs. Relationship • What if a manager gets a discretionary budget which is the sum that covers all depts managed by that employee? Then the previous ER diagram will cause – Redundancy of dbudget, where the same value is stored for each dept managed by the same manager. – Misleading as it suggests dbudget tied to managed dept rather than employee. • Instead a separate entity Mgr_Appts can be used with apptnum as key. This way the redundancy is eliminated! CENG 351

Entity vs. Relationship name ssn dname lot Employees did Manages 3 budget Departments since Entity vs. Relationship name ssn dname lot Employees did Manages 3 budget Departments since apptnum Mgr_Appts dbudget CENG 351

Binary vs. Ternary Relationships • If each policy is owned by just 1 employee: Binary vs. Ternary Relationships • If each policy is owned by just 1 employee: – Key constraint on Policies would mean policy can only cover 1 dependent! • • Every policy must be owned by some employee Dependents is a weak entity set Thus, this can be represented as follows: This ternary relationship is a poor or inappropriate design! ssn name pname lot Employees Dependents Covers Policies policyid age cost CENG 351

Binary vs. Ternary Relationships(cont. ) • Are statement of the same application with more Binary vs. Ternary Relationships(cont. ) • Are statement of the same application with more clarity: – A policy cannot be owned by more than one employee, every policy must be owned by some employee (total participation) and – Every policy must cover at least one dependent, dependent is a weak entity set. • Use a Beneficiary relationship which is an identifying relationship for the weak entity Dependents, ie. where each dependent will have one policy only… CENG 351

Binary vs. Ternary Relationships (cont. ) ssn name lot pname Employees age Dependents Purchaser Binary vs. Ternary Relationships (cont. ) ssn name lot pname Employees age Dependents Purchaser Beneficiary Better design policyid Policies cost CENG 351 With two binary relationships

Binary vs. Ternary Relationships (Cont. ) • The key constraints allow us to combine Binary vs. Ternary Relationships (Cont. ) • The key constraints allow us to combine Purchaser with Policies and Beneficiary with Dependents. • Participation constraints lead to NOT NULL constraints. • This example illustrated a case when 2 binary relationships were better than a ternary relationship. • It is also possible that ternary relationship is more suitable than binary, in some cases: CENG 351

Summary of Conceptual Design • Conceptual design follows requirements analysis, – Yields a high-level Summary of Conceptual Design • Conceptual design follows requirements analysis, – Yields a high-level description of data to be stored • ER model popular for conceptual design – Constructs are expressive, close to the way people think about their applications. • Basic constructs: entities, relationships, and attributes (of entities and relationships). • Some additional constructs: weak entities, ISA hierarchies, and aggregation. • Note: There are many variations on ER model. CENG 351

Summary of ER (Contd. ) • Several kinds of integrity constraints can be expressed Summary of ER (Contd. ) • Several kinds of integrity constraints can be expressed in the ER model: key constraints, participation constraints, and overlap/covering constraints for ISA hierarchies. Some foreign key constraints are also implicit in the definition of a relationship set. – Some of these constraints can be expressed in SQL. – Some constraints (notably, functional dependencies) cannot be expressed in the ER model. – Constraints play an important role in determining the best database design for an enterprise. CENG 351

Summary of ER (Contd. ) • ER design is subjective. There are often many Summary of ER (Contd. ) • ER design is subjective. There are often many ways to model a given scenario! Analyzing alternatives can be tricky, especially for a large enterprise. Common choices include: – Entity vs. attribute, entity vs. relationship, binary or n-ary relationship, whether or not to use ISA hierarchies, and whether or not to use aggregation. • Ensuring good database design: resulting relational schema should be analyzed and refined further. FD information and normalization techniques are especially useful. CENG 351

Relationship Set Ø The current “value” of an entity set is the set of Relationship Set Ø The current “value” of an entity set is the set of entities that belong to it. üExample: the set of all books in our database. Ø The “value” of a relationship is a set of lists of currently related entities, one from each of the related entity sets. CENG 351

Some of the Automated Database Design Tools COMPANY TOOL FUNCTIONALITY Embarcadero Technologies ER Studio Some of the Automated Database Design Tools COMPANY TOOL FUNCTIONALITY Embarcadero Technologies ER Studio Database Modeling in ER and IDEF 1 X DB Artisan Database administration and space and security management Oracle Developer 2000 and Designer 2000 Database modeling, application development Popkin Software System Architect 2001 Data modeling, object modeling, process modeling, structured analysis/design Platinum Technology Platinum Enterprice Modeling Suite: Erwin, BPWin, Paradigm Plus Data, process, and business component modeling Persistence Inc. Pwertier Mapping from O-O to relational model Rational Rose Modeling in UML and application generation in C++ and JAVA Rogue Ware RW Metro Mapping from O-O to relational model Resolution Ltd. Xcase Conceptual modeling up to code maintenance Sybase Enterprise Application Suite Data modeling, business logic modeling Visio Enterprise Data modeling, design and reengineering Visual Basi and Visual C++

Examples • A University database contains information about professors (identified by social security number, Examples • A University database contains information about professors (identified by social security number, or SSN) and courses (identified by courseid). Professors teach courses. • Each of the following situations concerns the Teaches relationship set. For each situation, draw an ER diagram that describes it (assuming that no further constraints hold). CENG 351

Examples (cont’) 1. Professors can teach the same course in several semesters, and each Examples (cont’) 1. Professors can teach the same course in several semesters, and each offering must be recorded. 2. Professors can teach the same course in several semesters, and only the most recent such offering needs to be recorded. (Assume this condition applies to all subsequent questions. ) 3. Every professor must teach some course 4. Every professor teaches exactly one course (no more, no less). 5. Every professor teaches exactly one course (no more no less), and every course must be taught by some professor. 6. Now assume that certain courses can be taught by a team of professors jointly, but it is possible that no one professor in a team can teach the course. Model this situation introducing additional entity sets and relationship sets if necessary. CENG 351

Example (cont’) 1) Professors can teach the same course in several semesters, and each Example (cont’) 1) Professors can teach the same course in several semesters, and each offering must be recorded. CENG 351

Example (cont’) 2) Professors can teach the same course in several semesters, and only Example (cont’) 2) Professors can teach the same course in several semesters, and only the most recent such offering needs to be recorded. (Assume this condition applies to all subsequent questions. ) CENG 351

Example (cont’) 3) Every professor must teach some course CENG 351 Example (cont’) 3) Every professor must teach some course CENG 351

Example (cont’) 4) Every professor teaches exactly one course (no more, no less). CENG Example (cont’) 4) Every professor teaches exactly one course (no more, no less). CENG 351

Example (cont’) 5) Every professor teaches exactly one course (no more no less), and Example (cont’) 5) Every professor teaches exactly one course (no more no less), and every course must be taught by some professor. CENG 351

Example (cont’) 6) Now assume that certain courses can be taught by a team Example (cont’) 6) Now assume that certain courses can be taught by a team of professors jointly, but no one professor in a team can teach the course alone. Model this situation introducing additional entity sets and relationship sets if necessary. CENG 351

CENG 351 CENG 351

Example • A company database needs to store information about employees (identified by ssn, Example • 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 must be 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. We are not interested in information about a child once the parent leaves the company. CENG 351

CENG 351 CENG 351

ER DIAGRAM FOR A BANK DATABASE v© The Benjamin/Cummings Publishing Company, Inc. 1994, Elmasri/Navathe, ER DIAGRAM FOR A BANK DATABASE v© The Benjamin/Cummings Publishing Company, Inc. 1994, Elmasri/Navathe, Fundamentals of Database Systems, Second Edition