Conceptual Design CB 16 CPSC 356 Database Ellen

Conceptual Design (CB 16) CPSC 356 Database Ellen Walker Hiram College (Includes figures from Database Systems by Connolly & Begg, © Addison Wesley 2002) )

Levels of Database Design • Conceptual database design – Identify important entities, relationships, and attributes • Logical database design – Translate concepts to logical structure (relations) • Physical database design – Determine how logical structure is physically implemented in the target DBMS

Critical Success Factors • Work interactively with the user as much as possible • Follow a structured methodology (process) • Be data-driven • Incorporate structural and integrity constraints • Use diagrams (e. g. ER) • Build a data dictionary • Be willing to repeat steps!

Conceptual Design Steps • Identify entity types • Identify relationship types • Identify and associate attributes for entities & relationships • Determine attribute domains • Determine key attributes (especially PK) • Check for redundancy • Validate against transactions • Review with user

Identifying Entity Types • Generally nouns and noun phrases in user specification • “Major” nouns -- if it qualifies another noun, it might be an attribute • But, attributes can’t have attributes (!) • Realize, initial decisions might have to be changed

Entities for Dream Home Dream. Home has branch offices in cities throughout the UK. Each office is allocated members of staff, including a Manager… Each branch office offers a range of properties for rent. … the management of a property is assigned to a member of staff whenever it is rented out …

Entities for Dream Home (cont’d) The details of property owners are also stored … The data stored for private owners includes owner number, name, address, telephone number, email and password. Dream. Home refers to members of the public interested in renting as clients. … When a property is rented out, a lease is drawn up between the client and the property…

Find the Entities in this 2004 Project: This database will be a campus-wide inventory of all plants on campus that would include information like GPS coordinates, growing hints, and uses. We also want to list all the gardens and their contents. We would also like to keep track of our members and useful things about them with respect to gardens.

Data Dictionary • Collects all information about each entity, e. g. – – Entity name Description Aliases (other names) Occurrence • Data dictionary serves as a blueprint, also helps identify duplication (synonyms)

Identifying Relationships • Look for verbs or verbal expressions in the specfication • Relationships must be between entities • Most relationships are binary • Consider implicit relationships – Check all pairs of entities ? – Missing relationships will show up in validation • Add relationships and multiplicity constraints to data dictionary

Relationships for Dream Home Dream. Home has branch offices in cities throughout the UK. Each office is allocated members of staff, including a Manager… Each branch office offers a range of properties for rent. … the management of a property is assigned to a member of staff whenever it is rented out …

Relationships for Dream Home (cont’d) The details of property owners are also stored … The data stored for private owners includes owner number, name, address, telephone number, email and password. Dream. Home refers to members of the public interested in renting as clients. … When a property is rented out, a lease is drawn up between the client and the property…

Data Dictionary Excerpt (Entities & Relationships) Entity Name Description Aliases Staff Describes anyone employed by Dream. Home Employee Each member of staff works at one branch Property. For. Rent A property that can be (or is) rented Property Connelly & Begg Figure 6. 1 Occurrences Each property has one owner and is available at one branch, where the property is managed by one member of staff. It can be viewed by many clients and rented by one client at a time.

Relationships in the 2004 Project? This database will be a campus-wide inventory of all plants on campus that would include information like GPS coordinates, growing hints, and uses. We also want to list all the gardens and their contents. We would also like to keep track of our members and useful things about them with respect to gardens.

Attributes • “What do we need to know / store about X? ” – X is an entity or relationship • Simple vs. composite attributes – Based on client’s needs – Can change later if needed – If composite, note the parts e. g. address. Street, address. City, address. Zip • Derived(computed) attributes – E. g. age derived from birthdate & current year

Attribute Example The details of property owners are also stored … The data stored for private owners includes owner number, name, address, telephone number, email and password.

Modifying Entities (Relationships vs. Attributes) • An entity cannot be an attribute of another entity - use a relationship instead • Multi-valued attributes are better expressed as relationships (e. g. phone #) – If you don’t do it now, that’s OK because it will be fixed later.

Documenting Attributes • • • Name and description Data type and length (domain) Aliases Simple attributes, if composite How to compute, if derived Default value, if any

Attributes in Data Dictionary Attribute Description Data Type Null OK? Multivalued? staff. No Unique id for staff member Integer No No name Composite; consists of fname, lname) fname First name 15 chars Yes (variable) No Lname Last name 30 chars No (variable) No position Job title 10 chars No (variable) No DOB Date of birth Date No Yes

Attributes in 2004 Project? • This database will be a campus-wide inventory of all plants on campus that would include information like GPS coordinates, growing hints, and uses. We also want to list all the gardens and their contents. We would also like to keep track of our members and useful things about them with respect to gardens.

Key Determination • Candidate keys are smallest sets of attributes that uniquely define an entity • Primary key is a chosen candidate key • Choose so that: – Minimal number of attributes – Values least likely to change – Smallest (e. g. fewest characters, smallest max value) – Easiest for user (search by…)

Which are Candidate Keys for Staff? Why (Not)? • • Fname? Lname? Fname, Lname? Lname, DOB? Fname, Lname, DOB? Staff. No, Lname?

Documentation • Document keys in data dictionary • Note weak entities (no key)

ER Diagram contains… • Entities (box – above line) • Attributes (box – below line) • Relationship (connecting line with arrow) – Cardinalities (x. . y at each end of line) – Relationship attributes (box with empty top, dotted line to relationship line)

Dream. Home ER Diagram Figure 16. 5

Check for Redundancy • Redundancy in a database is bad – Cost of multiple-entry – Risk of inconsistency • Recheck 1: 1 relationships – Should entities be merged? • Remove redundant relationships – If you can get from entity X to entity Y through 2 or more paths, consider whether relationships can be eliminated (direct vs. indirect) – Not all cycles should be eliminated, depending on meaning of the relationship [Document decisions]

Consider Transactions • A transaction is something the user wants to do with the database (e. g. print a student schedule) – Can the transaction be carried out using only the information in the ER diagram? – If so, you should be able to follow a path (of relationships) in the ER diagram to collect the data. (See p. 457)

Use Cases and Transactions • Each use case describes a transaction • Evaluate the model for each use case – Is there a missing path (relationship) to get the needed data? If so, consider what entities, relationships and/or attributes need to be added. – Are there portions of the diagram that are never used? If so, consider removing those entities, relationships, and/or attributes from the database.

Review Results with User • User should “sign off” that the model accurately reflects their understanding… • Do not expect the user to understand the ER diagram directly, without significant explanation from you • Review the data dictionary with the user to ensure you’ve got correct entities