The Entity-Relationship Model
Steps in Database Design
1) Requirement Analysis
Copyright By PowCoder代写 加微信 powcoder
– Identify the data that needs to be stored
• data requirements
– Identify the operations that need to be executed on
• functional requirements
2) Conceptual Database Design
– Use a semantic data model to develop
semantic schema
3) Map semantic schema to relational schema
Requirement Analysis
• Similar to first phase of general software design
• focus: data & functional requirements
• Questions to ask:
– What are the entities and relationships in the enterprise?
– What information about these entities and relationships should we store in the database?
– What are the integrity constraints or business rules that hold?
– What operations do we want to execute on the entities and
relationships?
• Tools known from SE
– data-flowdiagrams,sequencediagrams…
• In this course:
– half-formal,structuredspecificationinplainEnglish 3
Requirement Analysis: Minerva/Banner University Database
• Thedatabasecapturescrucialinformationabout university objects and subjects such as students, employees, financial data, courses and much more.
• Thedatacanbeaccessedviainterfacesthatallow
– students access to registration, address changes, fee
assessment, transcripts;
– Instructors access to enrollment information, grade input
– Principal researchers access to travel reimbursements …
– Administrative Personnel to access payroll …
– Employees access …
Requirement Analysis: Student Database
The database contains information about persons:
• eachpersonhasanidentifyingID
• eachpersonhasaPIN,name,permanentaddress+phonenumber,0or more emergency contacts
– Functionality
• alldatacanbeviewedandchanged
A person can be a student. A student has – Data:
– Functionality:
• Register for course…
A person can be an instructor.
– Functionality:
• Can teach a course • Can enter grades
Student Database (contd)
• A course
• Term: the term the course takes place • course id (special format)
• Credits: how many credits; typically 1-4 • title
• capacity
• enrollment
• one or more instructors
– Some properties are specific for one term; others always hold for this course
Student Database (contd)
• A course
– Functionality:
• Students can register
• Course can be cancelled
• Course can be assigned instructor
Semantic Data Model: Entity- Relationship Model (ER)
• The E/R model is a language that allows for a pictorially description of the data determined through the requirement analysis
– An E/R diagram or schema is a representation of the data model of the application
– An ER schema should be understandable by non-computer scientists.
• The main concepts of the E/R model are entities and relationships
• An ER schema can be translated into the relational schema in a
quite straightforward way.
• Conceptually similar to UML class diagrams
• Many dialects
8 – We use the one from the book….
• Entity: Real-world object distinguishable from other objects. An entity is described using a set of attributes.
– similar to an object/instance in OO
• Entity Set: A collection of similar entities, e.g., all companies registered in Quebec (similarity to a “class” in OO)
– All entities in an entity set have the same attributes (until we consider ISA hierarchies later…).An attribute describes a property of the entity set.
– An entity set must have a key: (underlined)
• Minimumsetofattributeswhosevaluesuniquelyidentifyanentityintheset
• WhatwouldbeagoodkeyforCompanies • Often:artificialkey
Entity set = rectangle, Attribute = oval
Basic Entity SetsàTables
BiggestEngCompanyEver
Eng. Av., H3X…
BiggestConstCommpanyEver
Constr. St. H4E…
NoNameCompany
Whatever St., …
ISA (“is a”) Hierarchies: Subclasses
• Subclass = special case = fewer entities = more properties
• “A ISA B”, then every A entity is also a B entity – KeyonlyinB.
Partnership
Corporation
ISA (“is a”) Hierarchies: Subclasses
• Reasons for using Subclasses:
• Additional descriptive attributes specific for a subclass
• Identification of a subclass that participates in a relationship (will see later)
• Where do the entities reside:
• E/R-viewpoint:: An entity has a component in each entity set to which
it logically belongs. Its properties are the union of these entity sets.
• Contrast OO-viewpoint:An object belongs to exactly one class.The subclass inherits the attributes of its superclass.
ISA (“is a”) Hierarchies: Keys Only the highest entity sets has the key attribute!!
Corporation name name
name Sole Partnership
ISA Hierarchies (Contd.)
• Overlap Constraint: Can an entity be in more than one subclass? (allowed/disallowed)
• Covering Constraint: Must every entity of the superclass be in one of the subclasses? (yes/no)
• Developing Class Hierarchies
– Specialization: Identifying subsets of an entity set (the superclass) that share some distinguishing characteristic. Represents top-down design: superclass is first, then subclasses are defined and specific attributes and relationships are set.
– Generalization: Several entity sets are generalized into a common entity set. Represents bottom-up design: common characteristics of a collection of entity sets are identified, a superclass entity set is built with these characteristics as attributes.
• Theoretically multiple inheritance possible, in practice not used 14
Relationship
• Relationship: Association among two or more entities. E.g. Company X has sponsored the Liberal Party (with a total amount of ….)
• Relationship Set: Collection of similar relationships
– An n-ary relationship set R relates n entity sets E1…En; each relationship in R
involves entities e1 Î E1, …. en Î En
• Can have attributes
Total amount
• Each entity of Companies can have relationships with many entities from Parties
– Company BiggestEngCompanyEver sponsors Liberals and PQ
• Each entity of Parties can have relationships with many entities
from Companies
– The Liberals are sponsored by BiggestEngCompanyEver and
BiggestConstCompanyEver
• Many-many relationship between Companies and Parties
• Each relationship is uniquely defined by the primary keys of
participating entities
– BiggestEngCompanyEver cannot sponsor twice the Liberals
– If we wanted to keep track of each sponsorship transaction, we would have to model this differently….
Key-constraints: one-many, many-one
• Belongs Relationship Set:
– Members (of the National Assembly) and (Political) Parties
– A member of the national assembly can belong to at most one party
– One party can have several members in the National Assembly
– The condition “each member belongs to only one party” is called a key constraint on the belong relationship set
• depicted with arrow from Members to Parties
Members belongs Parties
– Each member of the National Assembly can be leader of at
most one party
– Each party can have at most one leader
– One-to-one relationship set between Members and Parties
– Key constraints in both directions
name status
Note on Key Constraints
• Theexistence(ornon-existence)ofkeyconstraints has a large influence on the final database design
– Key constraints: less tables in the relational model
– No key constraints: more tables in the relational model
• Beforeindicatingkeyconstraints,makesurethatthey
really hold in all circumstances
– Invariants of the application
– in Germany, the Green party had for a long time always two leadersàkey constraint of last slide does not hold
• Ifitlaterturnsoutthatakeyconstraintdoesnothold, then your database design might have problems to capture all data
Participation Constraints
• Sponsor_2 participation:
– Each company must sponsor at least one party
• (we don’t keep track of companies that don’t provide sponsorship….)
– the participation constraint requires that the participation of Company in Sponsor is total (vs. partial) (depicted with a thick line)
Sponsors_2
Participation Constraints
• Belongs_2
• Each member belongs to a Party
• key constraint: at most once (exactly one Party)
• participation constraint: at least one
• combined: exactly once
Relationships contd.
Relationship between companies
Ex: Spin-off Alphabet ====== Google
Role indicator:
Parent company / spin-off
name status
Lobbyist Miller with id 007 builds the liaison for
Ternary Relationship
• BiggestEngFirmEver and the Liberal Party;
• BiggestConsFirmEver and the Liberals
• BiggestConsFirmEver and the CAQ
Lobbyist Max with id 008 builds the liaison for
• BiggestEngFirmEver and the Liberal Party;
Note that given relationshipset is many-many-many
Requirements Quiz
Weak Entities
tname ranking Shirt number
Teams plays Players
Weak Entities
tname ranking Shirt number
Teams plays Players
Weak Entities
• A weak entity “needs help” from another entity to be uniquely identifiable
• That is, a weak entity can be identified uniquely only by considering the primary key of another (owner) entity.
– The key in weak entity set is the union of the key of the owner entity set and a set of its own attributes (underlined with dashes)
– Owner entity set and weak entity set must participate in a supporting one-to-many relationship set (one owner, many weak entities).
– Weak entity set must have total participation in this identifying set.
Example: Players in Hockey Teams
• Player names are not unique
• Shirt numbers are not unique
– Same numbers on different teams
• But the shirt number on each team is unique
– Combination of team name and shirt number is unique
– Weak entity set in bold
– Relationship set to supporting entity set with key and participating
constraint (bold and arrowed)
– Relationship set in bold
– Partial key in weak entity set with dashed line
tname ranking Shirt number
Teams plays Players
Don’t overdo Weak Entities
• Many things appear to depend on entities of other entity sets and need their keys to identify.
• In reality:
– Often create artificial keys
– Examples
• Car and Owners
– Make License plate key
• Online users and their purchases
– Generate unique purchase order id
When to use weak entities
• If there is no global authority to create global identifiers
– Difficult to agree on unique player numbers across all NHL teams
Conceptual Design with ER
• Design Principles: – Keep it simple
• Don’t use an entity set when an attribute is sufficient – Avoid redundancies
– Capture as many constraints as possible
• Design Choices
– Entity or attribute?
– Attributes and relationships
– Identifying relationships: Binary or ternary?
Avoid Redundancies
don’t store information more than once Correct Design
Members belongs Parties
Each political party with its status is captured once as an entity in Political Parties
Avoid Redundancies
don’t store information more than once Bad Design
This design captures the name of the party twice. Once as attribute and once as entity
Avoid Redundancies
don’t store information more than once Bad Design
partyname status
This design indicates the status of a party for each member of the party, and looses the information for a party that temporarily does not have members.
Entity Set vs. Attribute
• A “thing” should satisfy at least one of the following to be represented as an entity set
It is more than the name of something; at least one non-key attribute
It is the many in a many-many or many-one
relationship set
• Example: A product can belong to many categories
Entity Set vs. Attribute
It is more than the name of something; at least one non-key attribute
Party has additional attribute status
Members belongs Parties
No extra information about the party except the name (and a
member belongs to at most one party)
Entity Set vs. Attribute
2. It is the many in a many-many or many-one relationship set
• Example: A product can belong to many categories
Categories
• A product belongs to only one category (and category is simply a name)
Relationships and Attributes
Total amount
Total amount
Relationships vs. Entity Sets
Ternary (payment as relationship set) vs. 3 binary
relationship sets (payment as entity set)
Relationships vs. Entity Sets
payment as entity set allows several payments of the same
company to the same party by the same connection
ISA (“is a”) Hierarchies
• Subclasses
– Have extra attributes that only belong to the subclass OR – Have a relationship set that only holds for the subclass
Partnership
Corporation
Redundant Attributes
• Attributes seats carries redundant information • Why?
• Keep it nevertheless??
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com