Designing Database Applications Walid G. Aref Roadmap for - - PowerPoint PPT Presentation

Designing Database Applications

Walid G. Aref

Roadmap for Designing Database Applications

1. Analyzing real-world applications

• Real-world application side: Domain experts
• To tell their story – What they need
• DBMS side: System analyst
• Map the domain expert needs to a model that the database developers can use to Design the

database application

• Use Modeling Language
• UML (The Unified Modeling Language ): A general-purpose, developmental, modeling language that is

intended to provide a standard way to visualize the design of a system

• ER Model (The Entity-Relationship Model):
• High-level data model
• Define the data elements and relationships for a specified system
• Provide a conceptual design for the database
• Very simple and easy to design view of data.
• Output from this process: High-level Design Document
• Includes ER diagrams
• Mockup screen designs (to reflect update screens, query screens, reports screens)
• Workflows of processes

Roadmap for Designing Database Applications

• 2. Transferring the design document to the relational model
• Input: Design Document (ER Diagrams)
• Output: Relational schemas
• Tools: DB Design tools (or manually)
• 3. Eliminating Redundancy
• Input: Relational schemas
• Output: Optimized relational schemas
• Mathematical foundation: Normalization Theory
• Tools: DB Design tools (or manually)
• 4. Developing the database application software
• Input: Optimized relational schemas, screen designs, process workflows
• Output: Database Application

Database Design Progress: Step 1 – Entity Relationship Modeling

• Analyze the Real-world Scenario
• Use the Entity-Relationship Model (ER Model) in this Step
• Output is Conceptual Design Document
• Identify the Entities and the Relationships in the Real-world Scenario
• Identify the Attributes of both the Entities and the Relationships
• Reflects the information we want to store in the database about these entities and

relationships

• Identify the integrity constraints that need to hold for this real-world

application

• Produce a pictorial database "schema” (ER diagram)

Building Blocks of the ER Model

• Entities (E) and Relationships (R)
• The ER Diagram represents the design
• Entity:
• Real-world object that is of interest in the scope of this real-world scenario.
• Example entities: Instructor, Student, Course, Classroom, Textbook, Restaurant
• Each entity is described using a collection of attributes
• Attribute:
• Describes an entity
• Example attributes for Entity Instructor: iid, iName, iRank, iSalary, iOfficeNumber,

iPhoneNumber, iAddress

• Each attribute has a type (domain)
• Entity Set:
• A class or group of entities that share the same attributes, e.g., the set of instructors
• Each entity set has one or more attributes that serve as the key
• ER Diagram:
• Entity Set shows as a Rectangle
• Attribute shows as an Oval
• Key of the Entity set is underlined, e.g., iid in the figure

Instructor iid iName iRank

iSalary

Our Example Relational Database Schema

• Students(sid: string, name: string, login: string, age: integer, gpa: real)
• Courses(cid: string, cname: string, credits: integer)
• Enrolled(sid: string, cid: string, grade: string)
• Instructor(iid: string, iname: string, irank: string, isalary: real)
• Teaches(iid: string, cid: string, year: integer, semester: string)

6

En Entity y Sets s for Our Example Relational Database Schema

• Students(sid: string, name: string, login: string, age: integer, gpa: real)
• Courses(cid: string, cname: string, credits: integer)
• Enrolled(sid: string, cid: string, grade: string)
• Instructor(iid: string, iname: string, irank: string, isalary: real)
• Teaches(iid: string, cid: string, year: integer, semester: string)

7

Relationships

• A Relationship associates two or more entities together
• A relationship can have attributes
• A Relationship Set: A set of similar relationships among the same

group of entities with the same attributes

• Binary and n-ary relationship sets
• An entity set can participate in multiple relationship sets
• An entity set can participate in the same relationship set with

different “roles”

• ER Diagram: Relationship show as a diamond

R1 E2 E1 Binary Relationship

Manage Employee

Manager of Managed by

Re Relationship Sets in Our Example Relational Database Schema

• Students(sid: string, name: string, login: string, age: integer, gpa: real)
• Courses(cid: string, cname: string, credits: integer)
• Enrolled(sid: string, cid: string, grade: string)
• Instructor(iid: string, iname: string, irank: string, isalary: real)
• Teaches(iid: string, cid: string, year: integer, semester: string)

9

Teaches

Instructor iid iName iRank

iSalary

Courses cid

semester

cname

year credits

Examples of Ternary Relationships

• This student is taking this course with this instructor in this semester

Takes

Courses

semester year

Instructor Student

Examples of Ternary Relationships

• This student is taking this course with this instructor in this semester

1st Entity 2nd Entity 3rd Entity Attribute

• Can semester be an entity as well?

Takes

Courses

semester year

Instructor Student

Can we Model a Ternary Relationship with Multiple Binary Relationships?

• Will need to add a new entity
• Each course offering will represent a triplet
• Degree of a relationship = # of entities

Takes

Courses

semester year

Instructor Student Course Offering

R1

Instructor Student

R2

Courses

R3 semester year

Cardinality Constraints in Relationship Sets

• 1-1, 1-m, m-1, m-m relationships
• An element may not participate in

the relationship

• For example,
• => Partial participation

Many-to-Many Relationship 1-to-1 Relationship 1-to Many Relationship Many-to-1 Relationship

Entity

Cardinality Constraints in Relationship Sets

• Total Participation:
• Every entity in the entity set participates in the relationship
• Example: Every course should have an instructor
• Thus, every course participates in the Teaches relationship set

Teaches

Instructor iid iName iRank

iSalary

Courses cid

semester

cname

year credits

Cardinality Constraints in Relationship Sets

• Total Participation:
• Every entity in the entity set participates in the relationship
• Example: Every course should have an instructor
• Thus, every course participates in the Teaches relationship set
• ER Diagram:
• Use bold line to indicate total participation

Teaches

Instructor iid iName iRank

iSalary

Courses cid

semester

cname

year credits

Cardinality Constraints in Relationship Sets

• One-to-one relationship:
• Instructor can teach at most one course
• ER Diagram:
• Use arrow at Courses side

Teaches

Instructor iid iName iRank

iSalary

Courses cid

semester

cname

year credits

Cardinality Constraints in Relationship Sets

• One-to-one relationship:
• Instructor can teach at most one course
• A course can be taught by at most one instructor
• ER Diagram:
• Use arrow at Courses side
• Use arrow at Instructor’s side

Teaches

Instructor iid iName iRank

iSalary

Courses cid

semester

cname

year credits

Cardinality Constraints in Relationship Sets

• One-to-Many Relationships
• A course can be taught by at most one instructor
• An instructor can teach more than one course
• ER Diagram:
• Use arrow at Instructor’s side (One side)
• Use straight line at Courses side (Many side)

Teaches

Instructor iid iName iRank

iSalary

Courses cid

semester

cname

year credits

Cardinality Constraints in Relationship Sets

• Many-to-One Relationship
• A course can be taught by more than one instructor
• An instructor can teach at most one course
• ER Diagram:
• Use arrow at Courses side (One side)
• Use straight line at Instructor’s side (Many side)

Teaches

Instructor iid iName iRank

iSalary

Courses cid

semester

cname

year credits

Cardinality Constraints in Relationship Sets

• Many-to-Many Relationships
• An instructor can teach more than one course
• A course can be taught by more than one instructor
• ER Diagram:
• Use straight line at Courses side (Many side)
• Use straight line at Instructor’s side (Many side)

Teaches

Instructor iid iName iRank

iSalary

Courses cid

semester

cname

year credits

Example of Combining Cardinality Constraints in Relationship Sets

• One-to-Many Relationship + Total Participation
• Every course must be taught by exactly one instructor
• An instructor can teach more than one course
• ER Diagram:
• Use arrow at Instructor’s side (One side)
• Use bold straight line at Courses side (Many side)

Teaches

Instructor iid iName iRank

iSalary

Courses cid

semester

cname

year credits

More Complex Participation Constraints

• A line in one side of a relationship may be annotated by a minimum

and maximum cardinalities.

• Appears in the form: LowerLimit-UpperLimit
• LowerLimit
• A LowerLimit of 1 means total participation.
• A LowerLimit of 0 means partial participation.
• A LowerLimit of m means at least m participations in the relationship
• UpperLimit
• An UpperLimit of 1 means ”at most one relationship”.
• An UpperLimit of * means Many (no limit).
• An UpperLimit of n means at most n participations in the relationship

Example of More Complex Participation Constraints

• Every course must be taught by exactly one instructor
• Each instructor can teach at least two courses but no more than five

courses

• ER Diagram:

Teaches

Instructor iid iName iRank

iSalary

Courses cid

semester

cname

year credits 2-5 1-1

Weak Entity Sets

• A weak entity is one that can be identified uniquely only by considering the primary key of another (owner or

identifying or strong) entity.

• The owner entity set and the weak entity set must participate in a one-to-one or one-to-many relationship sets (one
• wner, many weak entities).
• The identifying relationship:
• relationship associating the weak entity set with the identifying entity set
• The Weak entity set must have total participation in this identifying relationship set.
• A weak entity mush have a discriminator key that identifies the entities associated with the same owner entity
• Example 1: Instructor has dependents
• Discriminator Key: Dep_name
• The pair (Dep_name,iid) is unique
• Example 2: Sections in course
• Section: weak entity
• Discriminator Key: Section#
• Key: Section#,iid
• ER-Diagram:
• Weak Entity and Identifying relationship in Bold

Dep_age Dep_name Dependents Insured Price

Instructor iid iName iRank

iSalary Year

Specializations and Generalizations (IS-A Hierarchies)

• IS-A Hierarchy: Class/Subclass hierarchy
• Specialization and generalization are used interchangeably
• Depends on whether your design is top-down or bottom up
• Specialization: Identify a subset within an entity set that have

some commonalities and that are distinguishable from other entities in the same entity set

• E.g., for the entity set instructor, we can identify (specialize) the

subset of Tenured Instructors vs. Adjunct Faculty

• The specialized subsets are lower-level entity sets that:
• Inherit all the attributes from the higher-level entity set
• Have additional attributes that are not shared with the higher-level

entity set.

• Can participate in relationships independent of the higher-level

entity set.

• Example:
• Tenured professor ”is-an” instructor “is a”
• Inherits all attributes of instructor
• Has an additional attribute: TenureYear
• ER Diagram: An IS-A triangle

Tenured_Prof hourly_wages IS-A Adjunct_Faculty TenureYear hours_worked

Instructor iid iName iRank

iSalary

Specializations Constraints

• Overlap Constraint: Can Adam be a Tenured faculty as well as an

Adjunct faculty at the same time? i.e., belong to multiple specializations?

• ER Diagram:
• Need to state if overlap is Allowed (Overlap) or disallowed (Disjoint)
• Covering constraints: Can there be an instructor that is neither a

tenured faculty nor an adjunct faculty?

• ER Diagram:
• Need to state if coverage is total or partial
• Affects the design of the underlying database
• For example: Partial implies that some entities will still belong to the

super class

• Will need to be store these entities separately

Tenured_Prof IS-A Adjunct_Faculty Instructor

Disjoint & Partial

Aggregation

• Use aggregation when we want to create a relationship

(e.g., Evaluates) that relates an entity to other relationships and entities (e.g., teaches, courses, and instructor)

• Aggregation treats a relationship set as an entity set

Teaches

Instructor iid iName iRank

iSalary

Courses cid

semester

cname

year credits

Evaluates Mentor

Date

• Can we use a Ternary

Relationship instead?

• Evaluates has its own

attributes

• Can restrict that each

Teaching activity is evaluated by at most

• ne mentor.
• Also, not every

teaching activity is

• evaluated. Thus, need

to retain the Teaches relationship

Attributes in The ER Model

• Simple (single valued) - - - - - -- - - - - - - - - - - - - - -- - - - - - - - --
• Each attribute has a Domain that determines the set of

feasible values for the attribute

• Composite - - - - - - - - -- - - - - - - - - - - - - - - - -- - - - -- - - - - - - -
• Can be a hierarchy of any dept
• Will contain component attributes - - - - - - - - -
• Multi-valued (e.g., multiple phone numbers as multiple value
• f one attribute - - - - - - - - - - -- - -- - - - - - - - - - - - - - - - - - - -
• ER Diagram Notation: Double ovals
• Derived: Can be derived from existing attributes (e.g., age

from BirthDate)

• ER Diagram Notation: Dashed Oval - - - - - - - - - - - - - - - -

Name Name First Name Middle Name Last Name Phone Numbers Age

Design Choices in the ER Model

• ER Modeling is a design process
• Some design choices:
• Entity vs. Attribute
• Should some concept be modeled as an entity or an attribute?
• E.g., composite attributes and multivalued attributed. Can they be entities instead?
• If an instructor has multiple phones, can make phones an entity set that is related to instructors
• Can Semester be an entity or an attribute?
• If sub-components of an address attribute are important, e.g., state, zip-code, city, street name, .., make

address an entity and each of the sub-components an attribute of the address entity. Otherwise, make address as an atomic attribute (whose value is indivisible)

• Entity vs. Relationship:
• Should a concept be modeled as an entity or a relationship?
• Arity of the Relationships:
• Binary or ternary?
• Aggregation vs. Relationship?
• Designs may differ

Entity vs. Attribute

• Works_In does not allow an

employee to work in a department for two or more periods.

• Similar to the problem of

wanting to record several addresses for an employee: We want to record several values of the descriptive attributes for each instance of this

• relationship. Accomplished by

introducing new entity set, Duration.

name Employees ssn lot Works_In from to dname budget did Departments dname budget did name Departments ssn lot Employees Works_In Duration from to

Database Management Systems, 3rd Edition, R. Ramakrishnan, and J. Gehrke

Entity vs. Relationship

• First ER diagram OK if 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.

Manages name dname budget did Employees Departments ssn lot dbudget since dname budget did Departments Manages Employees name ssn lot since Managers dbudget

ISA

This fixes the problem! Database Management Systems, 3rd Edition, R. Ramakrishnan, and J. Gehrke

Binary vs. Ternary Relationships

• If each policy is owned by just 1

employee, and each dependent is tied to the covering policy, first diagram is inaccurate.

• What are the additional

constraints in the 2nd diagram?

age pname Dependents Covers name Employees ssn lot Policies Policy_id cost Beneficiary age pname Dependents Policy_id cost Policies Purchaser name Employees ssn lot

Bad design Better design Database Management Systems, 3rd Edition, R. Ramakrishnan, and J. Gehrke

• Previous example illustrates a case when two binary

relationships were better than one ternary relationship.

• An example in the other direction: A ternary relation Contracts

relates entity sets Parts, Departments and Suppliers, and has 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?

Database Management Systems, 3rd Edition, R. Ramakrishnan, and J. Gehrke

Binary vs. Ternary Relationships

Translating the ER Diagram into the Relational Model

• Goal:
• Capture all the details and all the data
• Never lose data

Translating the ER Diagram into the Relational Model

• Entities with attributes
• Relationships
• Weak Entity Sets
• Specializations and Generalizations

Translating ER En Entity y Sets s into the Relational Model

• Entities with attributes = = > Make it a relation
• Map each Entity Set to a relation with the entity’s attributes being the

relation’s attributes

• Make the key for the relation the same as the key for the entity set.
• Instructor(iid: string, iname: string, irank: string, isalary: real)

Instructor iid iName iRank

iSalary

Translating ER Re Relationships into the Relational Model

• Same as an entity set, map a Relationship with its è

attributes into a relation.

• Add the attributes of the relationship as attributes è
• Add the keys of the participating entities and make

them keys in the relationship è

37

Teaches

Instructor iid iName iRank

iSalary

Courses cid

semester

cname

year credits

• Relation Teaches
• Teaches(year,

semester)

• Teaches(iid, cid,

year, semester)

Translating ER Re Relationships into the Relational Model

• Does the cardinality constraint of the relationship make a difference?
• Assume an m-1 relationship
• Example: E1(A,B) R1(A, C, F) E2(C,D)
• Table R1 is redundant
• m-1 è Each tuple in E1 participates only once in R1
• R1 is redundant è Merge Tables E1 and R1 into one Table
• Always applicable to m-1 and 1-m relationships
• Saves one Table (affects query processing significantly)
• Applies to 1-1 relationships. Add either id into the other table or
• Or merge the two tables into one table

R1 E2 E1 m-1 Relationship

F A B a1 b1 a2 b2 a3 b3 C D c1 d1 c2 d2 c3 d3 A C F a1 c1 f1 a2 c1 f2 a3 c3 f3 A B C F a1 b1 c1 f1 a2 b2 c1 f2 a3 b3 c3 f3 C D c1 d1 c2 d2 c3 d3

Foreign key

D C B A

Translating ER Re Relationships into the Relational Model (Note)

• For m-1 relationships without total participation
• Applying this optimization may result in null values
• Example 2: a3 does not participate in R1

E1(A,B) R1(A, C, F) E2(C,D)

A B a1 b1 a2 b2 a3 b3 C D c1 d1 c2 d2 c3 d3 A C F a1 c1 f1 a2 c1 f2 A B C F a1 b1 c1 f1 a2 b2 c1 f2 a3 b3 ⏚ ⏚ C D c1 d1 c2 d2 c3 d3

Foreign key has Null values R1 E2 E1 m-1 Relationship

F D C B A

Translating ER Re Relationships into the Relational Model (Note)

• When an Entity Set has multiple roles in a relationship
• E.g., Employee id will have to appear twice
• Will need to rename the attributes in the generated table
• E.g.,
• If Employee(Eid, eName, eAddress)
• To relate Employee to itself, will need to add Eid twice in the Manage

relation è The need for renaming

• Then, Manage (Eid, Mid)

Manage Employee

Manager of Managed by

Translating ER We Weak Entity Sets into the Relational Model

• Weak entity set è table
• Table includes a column for the primary

key of the identifying strong entity set

• Dependents (iid, Dep_name, Dep_age)
• Primary key: iid, Dep_name
• Foreign key: iid

Dep_age Dep_name Dependents Insured Price

Instructor iid iName iRank

iSalary Year

• Where does Attributes Price, Year go?
• If different for every dependent, then:
• Dependents (iid, Dep_name, Dep_age,

Price, Year)

• If the same for all dependents of an

instructor

• Should have been put in Instructor

Translating ER Sp Speci cialization

• ns into the

Relational Model

Tenured_Prof hourly_wages IS-A Adjunct_Faculty TenureYear hours_worked

Instructor iid iName iRank

iSalary

• Overlap Constraint: Can Adam be a

Tenured faculty as well as an Adjunct faculty at the same time? i.e., belong to multiple specializations?

• Overlap or Disjoint
• Covering constraints: Can there be an

instructor that is neither a tenured faculty nor an adjunct faculty?

• Total or Partial
• If Coverage is Partial
• Schema for the super-class entity (Instructor)
• Schema for each sub-class entity set, add

primary key of super-class entity set + attributes of subclass

• Drawback: To get name and TenureYear of

Tenured Prof need to access two relations, the one for the super-class (Instructor) and the one for the sub-class (Tenured_Prof)

• Instructor(iid, iName, iRank, iSalary)
• Adjunct_Faculty(iid, hourly_wages, hours_worked)
• Tenured_Prof(iid, TenureYear)
• Works also for total coverage

Translating ER Sp Speci cialization

• ns into the

Relational Model (Alternative Way)

Tenured_Prof hourly_wages IS-A Adjunct_Faculty TenureYear hours_worked

Instructor iid iName iRank

iSalary

• Instructor(iid, iName, iRank,

iSalary)

• Adjunct_Faculty(iid, iName, iRank,

iSalary, hourly_wages, hours_worked)

• Tenured_Prof(iid, iName, iRank,

iSalary, TenureYear)

• Drawback: iName, iRank, iSalary

may be stored redundantly for people who are both instructor and Tenured_Prof

• If total and disjoint partitioning, no

need for relation Instructor

• But to query Instructor, will need to

always two tables

Translating ER Sp Speci cialization

• ns into the

Relational Model (Alternative Way 2)

Tenured_Prof hourly_wages IS-A Adjunct_Faculty TenureYear hours_worked

Instructor iid iName iRank

iSalary

• Make one fat schema for

everything

• Instructor(iid, iName, iRank,

iSalary, Adjunct_Faculty, hourly_wages, hours_worked, Tenured_Prof, TenureYear)

• Adjunct_Faculty and

Tenured_Prof are Boolean

• Will have Null values

Translating ER Comp Compos

• site Attri

ributes into the Relational Model

• Composite Attributes
• Flatten into the entity
• Instructor(iid, fName, mName,

lName, iRank, iSalary)

First Name Middle Name Last Name

Instructor iid iName iRank

iSalary

Translating ER Comp Compos

• site Attri

ributes into the Relational Model

• Multi-valued Attributes:
• Two approaches:

1. Take phone numbers out of Instructor schema, Create a separate phone table: Phones(iid, phone) 2. If we happen to know max number of phones per instructor (e.g., 3 phones), then: Instructor(iid, fName, mName, lName, iRank, iSalary, phone1, phone2, phone3) May introduce Null Values

First Name Middle Name Last Name

Instructor iid iName iRank

iSalary Phone Numbers