2

pdf

School

Simon Fraser University *

*We aren’t endorsed by this school

Course

354

Subject

Information Systems

Date

Oct 30, 2023

Type

pdf

Pages

61

Uploaded by Sahajkaran21

Report
CMPT 354 Conceptual Design Using the Entity-Relationship (ER) Model: Modelling The Real World Chapter 2
CMPT 354 Goal of Database Design Produce a set of tables that can store the data required by an enterprise Relational database tables represent – Entities, and – Relationships between entities A student database might contain – The entities: student and course and – The relationship: student takes course
CMPT 354 Overview of Database Design Conceptual design : ER Model is used at this stage. – What are the entities and relationships in the enterprise? – What information about them should we store in the database? – What are the integrity constraints or business rules that hold? – The ER model represent these pictorially ( ER diagrams ). – Map an ER diagram into a relational schema. Schema Refinement : (Normalization) Check relational schema for redundancies and related anomalies. Physical Database Design and Tuning : Consider typical workloads and further refine the database design.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 The Entity Relationship Model The ER model – Entity sets – Relationship sets (primary key, foreign keys, key constraints, total participation) – Weak entity sets – Subclasses – Aggregation ER design issues
CMPT 354 The Entity-Relationship Model The ER model has similarities to other modeling languages like UML The major components of the ER Model are – Entities such as people, cars, accounts, things, … – Attributes that describe the entities name, age, amount, date, … – Relationships that connect the entities customer owns account, student takes course – Constraints which restrict relationships an account must be owned by a customer nouns verbs
CMPT 354 ER Model Basics Entity : Real-world object distinguishable from other objects. – An entity is described 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 attribute has a domain . – Each entity set has a primary key . – Can map entity set to a relation easily. CREATE TABLE Employees (ssn CHAR (11), name CHAR (20), lot INTEGER , PRIMARY KEY Employees ssn name lot ssn name lot 123-22-3666 Attishoo 48 231-31-5368 Smiley 22 131-24-3650 Smethurst 35 Can you identify schema, instance, and IC (integrity constraint)?
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 ER Model Basics (Contd.) Relationship : Association among 2 or more entities. – Smith works in Pharmacy department. lot dname budget did since name Works_In Departments Employees ssn
CMPT 354 ER Model Basics (Contd.) Relationship Set : all similar relationships., e.g., all “Works_in” relationships between employees and departments. lot dname budget did since name Works_In Departments Employees ssn
CMPT 354 ER Model Basics (Contd.) n-ary relationship set R relates n entity sets E1 ... En; each relationship in R involves entities e1 E1, ..., en En Same entity set may participate in different relationship sets, or in different “roles” in same set. Works_In Departments Employees Reports_To lot name Employees subor- dinate super- visor ssn Manages
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 1 ER Model Basics (Contd.) Relationship sets can have descriptive attributes (e.g., the since attribute of Works_In). The relation for a relationship set must include the following attributes: – Primary keys for each participating entity set (as foreign keys ). – All descriptive attributes. CREATE TABLE Works_In( ssn CHAR (1), did INTEGER , since DATE ) ssn did since 123-22-3666 51 1/1/91 123-22-3666 56 3/3/93 231-31-5368 51 2/2/92 lot dname budget did since name Works_In Departments Employees ssn
CMPT 354 1 Foreign Key Constraints CREATE TABLE Works_In( FOREIGN KEY (ssn) REFERENCES Employees, ON DELETE NO ACTION) • It states that each ssn in Works_In must occur in Employees. – ON DELETE NO ACTION: when deleting ssn from Employees, deny the deletion. – ON DELETE CASCADE: when deleting ssn from Employees, delete ssn from Work_In as well. ssn did since 123-22-3666 51 1/1/91 123-22-3666 56 3/3/93 231-31-5368 51 2/2/92 ssn name lot 123-22-3666 Attishoo 48 231-31-5368 Smiley 22 131-24-3650 Smethurst 35 Works_In Employees
CMPT 354 1 ER Model Basics (Contd.) CREATE TABLE Works_In( ssn CHAR (1), did INTEGER , since DATE , PRIMARY KEY (ssn, did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments ) lot dname budget did since name Works_In Departments Employees ssn
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CREATE TABLE Manages ( managerSIN CHAR(11), subordinateSIN CHAR(11), FOREIGN KEY (managerSIN) REFERENCES Employee, FOREIGN KEY (subordinateSIN) REFERENCES Employee, PRIMARY KEY (managerSIN, subordinateSIN) ) Employee manages ssn name manages subordinat e lot
If each department has several locations, we must record an association among an employee, a department, and a location.
CMPT 354 1 Superkey and Candidate Key A superkey is any set of attributes whose values uniquely identify an entity in an entity set – { SIN } is a superkey for the Canadian Person entity set, so is { SIN , last name }. A candidate key is a minimal superkey, i.e., a superkey with no extraneous attributes – The superkey { SIN , last name } is not a candidate key because last name is not required to identify citizens. { SIN } is a candidate key.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 1 Relationship Set Primary Keys Primary key of a relationship set is derived from the primary keys of its entity sets and the type of relationship sets: – Many-to-many – One-to-many or many-to-one – One-to-one
CMPT 354 1 Many-to-Many Many-to-Many : each employee can work in many departments; each dept can have many employees. dname budget did since lot name ssn Works_In Employees Departments Many-to-Many CREATE TABLE Works_In ( ssn CHAR(11) , did INTEGER , since DATE , PRIMARY KEY (ssn,did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments)
CMPT 354 1 Many-to-Many The primary key (ssn,did) specifies that each relationship has only one “since”. Adding “since” to the key allows more than one “since”. dname budget did since lot name ssn Works_In Employees Departments CREATE TABLE Works_In ( ssn CHAR(11) , did INTEGER , since DATE , PRIMARY KEY (ssn,did, since), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments) ssn did since 123-22- 3666 51 1/1/91 123-22- 3666 51 3/3/93 231-31- 5368 56 2/2/92
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 1 Key Constraints 1-to-Many : each employee can manage many dept; each dept has at most one manager – did is the key now! - key constraint dname budget did since lot name ssn Manages Employees Departments 1-to Many CREATE TABLE Manages ( ssn CHAR(11) , did INTEGER , since DATE , PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments Key constraint
CMPT 354 2 Translating ER Diagrams with Key Constraints Since each department has at most one manager, we could store managing information directly in Departments. Possible only for key constraints CREATE TABLE Manages( ssn CHAR(11) , did INTEGER , since DATE , PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments CREATE TABLE Dept_Mgr( did INTEGER, dname CHAR(20), budget REAL, ssn CHAR(11) , since DATE , PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees Merged into Departments Department information Managing informaion
CMPT 354 2 Key Constraints Many-to-1 : each employee can manage at most one dept. 1-to-1 : each employee manages at most one department, and each dept has at most one manager ; Many-to-1 dname budget did since lot name ssn Manages Employees Departments 1-to-1
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Each employee works in at most one department at a single location.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 2 Participation Constraints Does every department have a manager? – If so, the participation of Departments in Manages is said to be total . Every did value in Departments table must appear in a row of the Manages table. lot name dname budget did since name dname budget did since Manages since Departments Employees ssn Works_In Total participation
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 2 Non-total participation of Employees ssn did since 123-22-3666 51 1/1/91 123-22-3666 56 3/3/93 231-31-5368 51 2/2/92 ssn name lot 123-22-3666 Attishoo 48 231-31-5368 Smiley 22 131-24-3650 Smethurst 35 Manages Employee s
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 2 Participation Constraints in SQL When both key constraint and total participation constraint are present: every dept has at most one and at least one manager. CREATE TABLE Dept_Mgr( did INTEGER, dname CHAR(20) , budget REAL , ssn CHAR(11) NOT NULL , since DATE , PRIMARY KEY (did) , FOREIGN KEY (ssn) REFERENCES Employees, ON DELETE NO ACTION ) Key constraint Participation constraint
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
A law firm’s lawyers are assigned to represent clients’ interests represents Lawyer Client
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Children attend pre-school, assume that A pre-school must have children Children can only attend one pre-school attends Child Pre-school
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Employees work for companies Employees must be employed by someone ▪ Or they wouldn’t be employees People may have more than one job worksFor Employee Company
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Chief Executive Officer (CEOs) of a company Being a CEO is a full-time position and A company can only have one CEO One person must be in charge ▪ So no co-ops or workers’ collectives is_CEO Employee Company
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Bank accounts and branches A branch can have many accounts An account must be held at a single branch holds Branch Account
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Rooms in a house This one should be easy … But how do we identify a room? What are its attributes and its primary key? contains House Room
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Weak Entity Sets The entities cannot be uniquely identified without information from a related entity set Subclasses Class hierarchies of entities Aggregation Used to model relationships that exist between entities and relationships
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 3 Weak Entities A weak entity can be identified uniquely only by considering another ( owner ) entity. – Dependents is a weak entity and Employees is the owner entity. – Each weak entity has at most one (key constraint) and at least one (total participation) owner entity in the identifying relationship . lot name age pname Dependents Employees ssn Policy cost Why not make dependents an attribute of Employees? Partial key
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 3 Weak Entity Sets Weak entity set and identifying relationship set are represented by a single table ( key constraint ) If the owner entity is deleted, all owned weak entities must also be deleted ( total participation ) CREATE TABLE Dep_Policy ( pname CHAR(20) , age INTEGER , cost REAL , ssn INTEGER NOT NULL , PRIMARY KEY (pname, ssn), FOREIGN KEY (ssn) REFERENCES Employees, ON DELETE CASCADE ) Not needed ssn is needed Compare this with the next slide
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 3 Participation Constraints in SQL When both key constraint and total participation constraint are present: CREATE TABLE Dept_Mgr( did INTEGER, dname CHAR(20) , budget REAL , ssn CHAR(11) NOT NULL , since DATE , PRIMARY KEY (did) , FOREIGN KEY (ssn) REFERENCES Employees, ON DELETE NO ACTION ) Key constraint Participation constraint
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Contains House Room name Identifies the partial key number street city squareFee t Identifies the weak entity set contains
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 3 ISA (`is a’) Hierarchies Contract_Emps name ssn Employees lot hourly_wages ISA Hourly_Emps contractid hours_worked A ISA B: every A entity is also a B entity, and A can have more attributes and participate in a specific relationship. Overlap constraints : Can Joe be an Hourly_Emps as well as a Contract_Emps entity? ( Allowed/disallowed ) Covering constraints : Does every Employees entity also have to be an Hourly_Emps or a Contract_Emps entity? (Yes/no)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 3 General approach: – 3 relations: Employees, Hourly_Emps and Contract_Emps. Every employee is recorded in Employees. Extra info recorded in Hourly_Emps; must delete Hourly_Emps tuple if referenced Employees tuple is deleted. Querying all employees easy, querying Hourly_Emps employees require a join to get some attributes. Contract_Emps name ssn Employees lot hourly_wages ISA Hourly_Emps contractid hours_worked Employees(ssn,name,lot) Hourly_Emps(ssn, hourly_wages, hours_worked)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 3 Alternative: Just Hourly_Emps and Contract_Emps. – Hourly_Emps : ssn , name, lot, hourly_wages, hours_worked. – Each employee must be in one of these two subclasses . Contract_Emps name ssn Employees lot hourly_wages ISA Hourly_Emps contractid hours_worked
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 4 Why Use Class Hierarchies? Attributes in common don’t have to be re-defined for each subclass Additional descriptive attributes can be added to subclasses To identify the set of entities that participate in a particular relationship – i.e., subclasses can be created to identify a relationship with another entity set
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 4 Aggregation Aggregation allows us to treat a relationship set (Sponsors) as an entity set for purposes of participation in (other) relationships. budget did pid started_on pbudget dname until Departments Projects Sponsors Employees Monitors lot name ssn since
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 4 Aggregation Monitors mapped to table like any other relationship set: budget did pid started_on pbudget dname until Departments Projects Sponsors Employees Monitors lot name ssn CREATE TABLE Monitors ( ssn, pid, did, until PRIMARY KEY (ssn,pid,did), FOREIGN KEY (ssn) REFERENCES Employees , FOREIGN KEY (pid,did) REFERENCES Sponsors )
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 4 Aggregation vs. ternary relationship Monitors and Sponsors are two distinct relationships with their own descriptive attributes. Can say each sponsorship is monitored by at most one employee. budget did pid started_on pbudget dname until Departments Projects Sponsors Employees Employees Monitors lot name ssn Projects Departments Vs 3-ary relationship:
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 4 ER Design Principles Faithfulness – The design must be faithful to the specification and the enterprise that is being represented – All the relevant aspects of the enterprise should be represented in the model Avoid redundancy – Redundant representation makes the ER diagram harder to understand – Redundancy wastes storage in the DB and May lead to inconsistencies
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 4 More ER Design Principles Simplicity – The simpler it is, the easier it is to understand – Avoid unnecessary entities or relationships – Where possible use attributes rather than entity sets or relationships As many constraints as possible – Primary key, key constraints, foreign key constraints, participation constraints – Some constraints cannot be shown in ER diagrams
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 4 Good: Beers ManfBy Manfs name address name
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 4 Bad: repeat manufacturer for each beer they manufacture Beers namef Manf_addr name
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 4 bad: manufacture’s name said twice. Beers ManfBy Manfs name address name manf
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 4 Conceptual Design Using the ER Model Design choices: – Should a concept be modelled as an entity or an attribute? – Should a concept be modelled 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 refining the schema: – Relational schema obtained from ER diagram is a good first step. But ER design subjective & can’t express certain constraints.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 5 Entity vs. Attribute Should address be an attribute of Employees or an entity (connected to Employees by a relationship)? Depends upon the use of address information: 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 by city, address must be modelled as an entity (since attribute values are atomic).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 5 Entity vs. Attribute (Contd.) Works_In2 does not allow an employee to work in a department for two or more periods (why?). Works_In3 allows to record several values of (from, to) for each Works_In relationship. name Employees ssn lot Works_In2 from to dname budget did Departments dname budget did name Departments ssn lot Employees Works_In3 Duration from to ssn did fro m to s1 d1 f1 t1 s1 d1 f2 t2
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 5 Entity vs. Relationship In first ER diagram, a manager gets a separate discretionary budget for each dept. What if a manager gets a discretionary budget that covers all managed depts? – Redundancy: dbudget stored for each dept managed by manager. – Misleading: suggests dbudget associated with department-mgr combination. Manages2 name dname budget did Employees Departments ssn lot dbudget since dname budget did Departments Manages2 Employees name ssn lot since Managers dbudget ISA This fixes the problem!
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 5 Binary vs. Ternary Relationships In the first ER, not possible to say that each policy is owned by just 1 employee and covers many dependents What are the additional constraints in the 2nd diagram? age pname Dependents Covers name Employees ssn lot Policies policyid cost Beneficiary age pname Dependents policyid cost Policies Purchaser name Employees ssn lot Bad design Better design
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 5 Binary vs. Ternary Relationships (Contd.) The key constraints allow us to combine Purchaser with Policies and Beneficiary with Dependents. Participation constraints lead to NOT NULL constraints. CREATE TABLE Policies ( policyid INTEGER , cost REAL , ssn CHAR(11) NOT NULL , PRIMARY KEY (policyid). FOREIGN KEY (ssn) REFERENCES Employees, ON DELETE CASCADE ) CREATE TABLE Dependents ( pname CHAR(20) , age INTEGER , policyid INTEGER , PRIMARY KEY (pname, policyid). FOREIGN KEY (policyid) REFERENCES Policies ON DELETE CASCADE ) What are the differences between above and here?
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 5 Binary vs. Ternary Relationships (Contd.) An example in the other direction: a ternary relation Contracts relates Parts, Departments and Suppliers , with the descriptive attribute qty . No combination of binary relationships is an adequate substitute: – S ``can-supply’’ P, D ``needs’’ P, and D ``deals- with’’ S does not imply that D has agreed to buy P from S. – How do we record qty ?
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
There are a number of alternative ER notations The most significant differences relate to how relationships and their cardinalities (i.e., key constraints) are expressed It is important to recognize this, and to check which version of an ER diagram is being used Some versions allow composite and multi- valued attributes This increases the expressive power of the model But makes it harder to translate the diagrams into a relational schema
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
R A B R A B many-to-many R A B R A B R A B one-to-one R A B R A B R A B many-to-one R A B * * 1 1 * 1 R A B R A B R A B M N 1 1 N 1
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 5 Summary of Conceptual Design Conceptual design follows requirements analysis , – Yield a high-level description of data to be stored – Model real world business rules (integrity constraints). Basic constructs: entities, relationships, attributes, constraints . Additional constructs: weak entities , ISA hierarchies , and aggregation . ER models are expressive, close to the way people think about their applications. Many variations on ER model and notations.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 5 Summary of ER (Contd.) Integrity constraints can be expressed in the ER model: primary key constraints , foreign key constraints, key constraints , participation constraints , and overlap/covering constraints for ISA hierarchies. More general constraints can be expressed in SQL using CHECK constraints (discussed later).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 6 Summary of ER (Contd.) ER design is subjective . Many ways to model a given scenario! Analysing alternatives can be tricky. 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. Resulting relational schema should be analysed and refined further. FD (functional dependency) information and normalisation techniques are useful here (discussed later).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
CMPT 354 6 Review Questions Model a real world scenario described in text using the ER model, stating the constraints modeled. Choose constructs among alternatives, justifying the choices. Translating a ER diagram into relational tables, covering basic and additional constructs, various constraints. Exercises: 2.3 and 2.7.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help

Related Documents

4247.pdf
Conflict-Mediation-Form.doc
Risk 8.png
Module 3 Labs.pdf
Module 10 Labs.pdf
Module 7 Labs.pdf
image.jpg
image.jpg
Module 9 Labs.pdf
Week 7.docx
Week 8.docx
Week 3.docx

Browse Popular Homework Q&A

Q: A Pitot tube measures a pressure difference Pa = P₁-P₁ (dynamic = total-static) between the front…
Q: antigun groups had a political impact on the mainstream in the last ten years,  how that has…
Q: Periodic inventory using FIFO, LIFO, and weighted average cost methods The units of an item…
Q: IUPAC name for Bi(BrO2)3
Q: Suppose an experiment of the Davisson-Germer variety is done using electrons and compared to another…
Q: An object of mass 2 kg is thrown straight upward with a velocity of 10 m/s. Neglecting air…
Q: Beta of a portfolio. The beta of four stocks-G, H, I, and J-are 0.42, 0.75, 1.19, and 1.65,…
Q: Write broadly on the importance of worker/working class forms of resistance for our history and for…
Q: What role does information technology play, and how does it impact, today's corporate communication…
Q: The graph below is the function f(x) -5 -4 -3 -2 -1 Find lim H-2 5 4 Find f(2) 3 2 1 -1 -2 -3 S 1 2…
Q: Inventory is not part of the liquid assets because it is mostly physical in nature.  True  False
Q: You can afford monthly deposits of $190 into an account that pays 3.6% compounded monthly. How long…
Q: Test the claim that the mean GPA of night students is smaller than 2.2 at the .005 significance…
Q: Draw a Bohr model for the following atoms: a. Neutral sulfur and sulfur ion. b. Magnesium-24 and…
Q: Determine if the specified linear transformation is (a) one-to-one and (b) onto. Justify each…
Q: Describe at least one criticism of Evolutionary Personality Psychology
Q: ZSM-5, a catalyst material, has a surface area of 425 m³/g. What is the surface area of this…
Q: personal growth introductio
Q: When the partial reflector of the microwave Fabry-Perot interferometer moves 8.3 cm the receiver…
Q: A distant point source of red light, a mask with two identical, very narrow slits, and a screen are…
Q: Perform the operation if possible. - 5 -4 - 9 - 6 0 6 4 1 -4 -2 6-3 Select the correct choice below…
Q: Scores on an IQ test are normally distributed. A sample of 20 iq scores had standard deviations s=…
Q: introduce saraswati
Q: A 200 g block on a frictionless surface is pushed against a spring with spring constant 500 N/m,…
Q: An inspector inspects a shipment of medications to determine the efficacy in terms of the proportion…