Transcript notes

Database Design
miniworld
Requirements & collection analysis
Database Requirements
refinement
Conceptual Design
Conceptual Schema (in a high-level data model)
DBMS independent
DBMS specific
Data Model Mapping
Conceptual Schema (in the model of a specific DBMS)
Physical Design
Internal Schema (in the model of a specific DBMS)
Security Design
Entity-Relationship (ER) model is a high level
conceptual data model.
• Entity: Real-world object distinguishable from
other objects.
Number=2311
• Attribute: A property of an entity.
Name=John Smith
(composite
attribute)
Address
Street Address
City=Houston
State=Texas
Zip=77546
Street=Kirby
Apt.=Null
BirthDate=3/17/1936
e1
Age=55 (derived attribute, deliverable from the BirthDate)
HomePhone=Null
Degree={B.S., M.S., Ph.D.} (Multivalued attribute)
Types of Attributes
• Simple attribute is not divisible (E.g., age).
• Composite attribute can be divided into smaller
subparts (e.g., Date (Month, Day, Year)).
Meaning of Null value: unknown or not applicable
• Complex attribute: Composite and multivalued attributes
can be nested and form a complex attribute.
CAR(Registration(RegistrationNumber, State), VehicleID, Make,
Model, Year, {color}).
Car1=((ABC123,TX),TK629,Saturn, SC1, 1993, {red,black})
• Entity Set (Entity Type): A collection of similar
entities. E.g., all employees.
•
Key Attribute: An attribute whose values are distinct
•
Domain: A set of all possible values for an attribute.
for each individual entity.
–
Example: The domain of Age attribute of entity type
EMPLOYEE can be the set of integer numbers between
16 and 70.
name
ssn
addr
student
cid
name
Course
Notation: entity set, attribute, key attribute
• Relationship: Association among two or more
entities.
• Example: Attishoo enrolled in COMS461 course
• Relationship Set: Collection of similar relationships.
• A relation may also have some attributes to describe the
properties of this relation
name
ssn
addr
student
entity set
semester
enrolled
cid
name
Course
relationship set
Notation: relationship set, attribute
• Same entity set could participate in different relationship
sets, or in different “roles” in same set.
• A relationship set can involve two or more entity sets
name
semester
addr
ssn
student
cid
name
course
enrolled
entity set
duration
Advised-by
teach
instructor
relationship set
ssn
name
addr
semester
Structural Constraints
• Cardinality Ratio Constraint specifies the
number of relationship instances that an
entity can participate in.
1-to-1
1-to Many
Many-to-1
Many-to-Many
•Key constraint specifies that each entity of an entity
set can participate in at most one relationship in a
relationship set.
since
name
ssn
dname
lot
Employees
did
Manages
budget
Departments
•Participation Constraint:
•Total participation: Every entity in the entity set
participates in the relationship set.
• E.g., Every department must have a manager.
• ER-Notation:
•Partial participation
Each
department
has at most
one manager.
E-R Model Exercises
Exercise
• A company has a number of employees. The
attributes of EMPLOYEE include Employee_ID
(identifier), Name, Address, and Birthdate.
• The company also has several projects. Attributes of
PROJECT include Project_ID (identifier),
Project_Name, and Start_Date.
• Each Employee may be assigned to one or more
projects, or may not be assigned to a project.
• A project must have at least one employee assigned,
and may have any number of employees assigned.
• An employee’s billing rate may vary by project, and
the company wishes to record the applicable billing
rate (Billing_Rate) for each employee when assigned
to a particular project.
Exercise
• A university has a large number of courses in its
catalog. Attributes of COURSE include
Course_number (identifier), Course_name, and Units.
• Each course may have one or more different courses
as prerequisites, or may have no prerequisites.
Similarly, a particular course may be a prerequisite
for any number of courses, or may not be
prerequisite for any other course.
Exercise
• A hospital has a large number of registered physicians and
patients.
• Attributes of PHYSICIAN include Physician_ID (identifier)
and Specialty.
• Attributes of Patients include Patient_ID (identifier) and
Patient_Name.
• Any patient who is admitted must have exactly one admitting
physician. A physician may optionally admit any number of
patients.
• Once admitted, a given patient must be treated by at least one
physician. A particular physician may treat any number of
patients, or may not treat any patients.
• Whenever a patient is treated by a physician, the hospital
wishes to record the details of the treatment
(Treatment_Detail). Components of Treatment_Detail include
Date, Time, and Results.
Weak Entities
• A weak entity can be identified uniquely only by
considering some of its attributes and the
primary key of another (owner) entity.
–
–
Weak entity set must have total participation in its
identifying relationship set.
Weak entity set must have key constraint.
name
ssn
lot
cost
Employees
Policy
Owner entity set
Identifying relationship set
pname
age
Dependents
Weak entity set
ISA (`is a’) Hierarchies
•
•
•
•
If we declare A ISA B, every A
entity is also considered to be a B
entity.
hourly_wages
Reasons for using ISA:
•To add attributes specific to a
subclass.
•To identify entities that
participate in a relationship.
name
ssn
lot
Employees
hours_worked
ISA
Hourly_Emps
contractid
Contract_Emps
Overlap constraints:
•
Can Joe be an Hourly_Emps as well as a Contract_Emps entity? (Allowed/disallowed)
•
•
Default value: no overlap;
Otherwise, write Hourly_Emps OVERLAPS Contract_emps
Covering constraints:
•
Does every Employees entity also have to be an Hourly_Emps or a Contract_Emps
entity? (Yes/no)
•
•
Default value: no;
Otherwise write Hourly_Emps and Contract_Emps COVER Employees
•
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.
–
ER diagrams can use different notations, but use the
same concept.
–
Several software tools are available for creating ER
diagrams: IBM Rational Rose, Microsoft Visio
Questions to think
•
Is there anything that cannot be described
by ER model?
Key Concepts of ER Model
•
Entity set
•
Relationship set
•
Constrains
•
Key constrain
•
Total participation constrain
•
Weak entity set
•
ISA
Database Requirements:
Maintain the following information about the employees of the company.
[Social security number, date of birth, name, gender, address, salary]
1.
Users want to be able to perform a search on either first name, last name, or middle
name.
2. An employee can have at most one supervisor who is also an employee, but a supervisor
can have more than one supervisees.
3. An employee can have dependents. The dependent name, gender, and date of birth, and
the relationship of the employee and his/her dependent are needed in the database.
4. The company is divided into departments. For each department, the department name,
department number, locations are needed in the database. A department can locate in
several locations.
5. A department can control several projects. Each project must be controlled by only
one department. The name of the project, the project number, and its location need to
be stored in the database.
6. Each employee works on at least one project and each project must have at least one
employee. The number of hours per week that an employee works on a project must be
recorded.
7. Each employee must work for only one department and the department must have at
least one employee. Users also want to query a number of employees in a department.
8. Each department must be managed by one employee. An employee can be a manager of
only one department.
9. The date when the employee becomes a manager for the department needs to be
maintained.
Database Requirements:
Maintain the following information for real estate firm
1. The firm has a number of sales offices in several states. Attributes of
sales offices include Office Number (identifier) and Location.
2. Each sales office is assigned one or more employees. Attributes of
employee include Employee ID (identifier) and Employee Name. An
employee must be assigned to only one sales office.
3. For each sales office, there is always one employee assigned to manage
that office.
4. The firm lists property for sale. Attributes of property include Property
ID (identifier) and Location. Components of Location include Address,
city, State, and Zip Code.
5. Each unit of property must be listed with one (and only one) of the sales
offices. A sales office may have any number of properties listed or may
have no properties listed.
6. Each unit of property has one ore more owners. Attributes of owners are
Owner ID (identifier) and Owner Name. An owner may own one or more
units of property. An attribute of the relationship between property and
owner is Percent Owned.