er-diagrams
TRANSCRIPT
1
Database Design
Victor Matos
2
Phases of Database Design
Conceptual design begins with the collection of requirements and results needed from the database (ER Diag.)
Logical schema is a description of the structure of the database (Relational, Network, etc.)
Physical schema is a description of the implementation (programs, tables, dictionaries, catalogs
DataRequirements
ConceptualDesign
LogicalDesign
PhysicalDesign
Conceptual Schema
Specification of requirementsand results
Logical Schema
Physical Schema
3
Models
A data model is a collection of objects that can be used to represent a set of data and operations to manipulate the data
Conceptual models are tools for representing reality at a very high-level of abstraction
Logical models are data descriptions that can be processed by computers
4
Conceptual model: Entity-Relationship Diagrams
Entities represent classes of real-world objects. Person, Students, Projects, Courses
are entities of a University database Relationships represent interactions
between two or more entities
Person
Is_Born_In
City
Lives_InN 1
N 1
5
Example:
Every employee works in at least one project
Every project has employees working on it.
EMPLOYEESSN
NameSalary
PROJECTNameCode
WORKS_ONNN
6
Higher-Order Relationships
A relationship may involve more than two entities
Course Meets Classroom
Day
7
Recursive relationships
Relationships could be mapped from one entity to itself
Manager_Of
Employee Manages
Subordinate_To
1
N
8
Attributes
Attributes represent elementary properties of the entities or relationships. The stored data will be kept as values of the attributes
PERSONSsn
NameProfession
Is_Born_In
CITYName
CountryElevation
Population
Lives_InN 1
N 1
Birth_Date
Moving_Date
9
Generalizations
MANDraft_Status
FEMALEMaiden_Name
PERSONSsn
NameAddress
• An entity could be seen from many different viewpoints
• Each viewpoint defines a set of roles in a generalization
• Example below uses SEX to classify the object “Person”
10
Generalizations
A classification could be disjoint or overlapping
An entity could have more than one classification
MANDraft_Status
FEMALEMaiden_Name
PERSONSsn
NameAddress
SEX
Full_TimeAnnual_Salary
Department
Part_TimeHour_Rate
Weekly_Hours
JOB
11
Example: Department Store 1/2
A department store operates in several cities In a city there is one headquarter coordinating
the local operations A city may have several stores Stores hold any amount of items Customers place their orders for any number of
items to a given store
GOAL: Optimize shopping in each city
12
Example: Department Store 2/2
CITIESCity Name
StateHeadQtr Address
STORESStore IDAddress
Manager ID
LOCATEDIN
ITEMSItem ID
DescriptionSizeColor
ORDERSOrder Number
Order Date
ITEMS ORDEREDQty Ordered
HOLDQty Held
IN-CITIESQty_in_cities
CUSTOMERCustomer Name
AddressPhone Number
PLACE
N
N
1
N
N N
N N N
1
13
Example: University Database
Professors work for only one department Departments have many professors Each course is taught by only one professor Students make a plan or program of study A course could meet in several rooms/times Graduate students must have an advisor Cities are categorized as resident/BirthPlace Visiting prof. need an End/Start date
14
University Database
University database
BIRTH_PLACECITYNameState
PERSONLastName
Age
PROFESSORTenureStatus
Title
STUDENT
DEPARTMENTNamePhone
Belongs_To
ENROLLEDSemester
Grade
PLANNEDSemester
GRADUATE_STUDENT ADVISED_BY
VISITING_PROFESSOR
Start_AppointmentEnd_Appointment
COURSETitle
DescriptionCredits
TAUGHT_BYSemester
MEETSROOM
RoomNumberBuilding
TIMEDayHour
RESIDENT
1 N
N
N
N
NN
N
1
N N
N 1
1
N
N
1
15
Soccer Database
A team has players, one coach, fans Teams play according to a schedule Teams need to practice in a Stadium Attendance and scores must be recorded
16
Soccer Database
FANName
AddressFavorite Bar
PLAYERUniform_Size
Shoe_Size
PERSONSsn
NameAddress
COACHPersonal RecordYears Experience
SUPPORTSPLAYS_FOR
MANAGES
TEAMName
LocationPhone
PLAY_AGAINST
GAMENumber
AttendanceFinal Score
Date
TAKESPLACE
AT
PRACTICES
STADIUMName
AddressCapacity
ATTENDS
N
N1
N 1
1
N N N 1
N
N
N N
17
Research Project Database
Some employees are researchers Every project has a leader investigator Every project must be funded by an agency A project may include several topics A topic could appear in several projects Researchers must produce report(s) Each employee must have a supervisor
18
Research Projects Database
RESEARCH TOPICCodeName
RESEARCH REPORTNumber
Title
EMPLOYEESSN
NameTitle
ADDRESSES PRODUCES
PRINCIPALINVESTIGATOR
RESEARCHPROJECT
NameCode
FUNDED_BYAmount
DateGrant#
AGENCYName
AddressContact Person
WORKS_ON
N
N
N
N
N
N
SUPERVISES
N 11
1 N
N
ON
N
NN
19
Contract-Supplies System 1/3
A company negotiates contracts with suppliers to provide certain amount of items at a fixed price
Orders are placed against any of the already negotiated contracts
A contract could provide items to any number of orders
20
Contract-Supplies System 2/3
An order may include any number of items negotiated in the contract
Orders should not exceed the maximum amount of items quoted in the contract
All items in an order must be provided as part of a single contract and a single project
21
Contract-Supplies System 3/3
SUPPLIERSSupplier Number
NameAddress
NEGOTIATEDate of Contract
CONTRACTSContract Number
Text
ITEMSItem NumberDescription
TO SUPPLYContract Price
Contract Amount
MADE OFOrder QTY
ORDERSOrder NumberDate Required
Date Completed
SUPPLIED AS
NEEDED BY
PROJECTSProject Number
Project Data
1
N
1 N
N
N
N N
1
N
22
Manufacturing: Requisitions 1/3
Projects are broken into tasks Tasks are assigned to departments A task is created for one project and
assigned to one department Requisitions are made for projects Each requisition could ask for any number
of items
23
Manufacturing: Requisitions 2/3
Each requisition is for one project and is made to one supplier
Items could be: equipment or materials and are coded in a similar way
Suppliers send periodic price notices to advise the company of any changes in their prices
24
Manufacturing: Requisitions 3/3
SUPPLIERSSupplierNumber
Supplier Data
PROJECTSProject Number
Project DataMADE OF
TASKSTask Number
Task Data
REQUESTS
REQUISITIONItem Number
Qty Requested
CONTAINS
ITEMSItem Number
Item Data
EQUIPMENT ITEMCode
Description
MATERIAL ITEMCode
DescriptionWeight
Container
CHANGESPRICE ADVICE
PriceEffective Date
SENDS
FROM
FOR
REQUISITIONItem Number
Qty Requested
1 N
N
1
N
1
N 1
N
N
1 N
N
1
25
Job-Shop Accounting System 1/4
The system is used by a company that manufactures special-purpose assemblies for customers
To manufacture assemblies the company creates a sequence of processes (steps)
Each process is supervised by a department Assume there are three type of processes:
PAINT, FIT, CUT
26
Job-Shop Accounting System 2/4
During manufacturing an assembly could pass through any sequence of processes, in any order; it may pass through the same process more than once
A unique Job# is given to any new assembly.
Costs and started-terminated dates must be recorded
27
Job-Shop Accounting System 3/4
An accounting system is used to keep track expenditure for each:
DEPT, PROCESS, ASSEMBLY
As a job proceeds, cost transactions can be recorded against it.
28
Job-Shop Accounting System 4/4
CUSTOMERS
Customer IDAddress
ORDERS
ASSEMBLIES
Assembly IDDate Ordered
Details
CUT JOBSCut Job ID
Machine typeused
Material usedLaor time
JOBSJob NumberStarting DateEnding DateTotal Cost
FIT JOBSFit Job IDLabor time
PAINT JOBSPaint Job ID
ColorVolume
Labor time
A1
ACTIVITY
USES
MADE AS
TRANSACTIONS
PROCESSACCOUNTS
Account 3Details
DEPTACCOUNTS
Account 2Details
ASSEMBLYACCOUNTS
Account 1Details
ACCOUNTSAccount ID
DateEstablished
PROCESSESProcess ID
Process Data
DEPARTMENTS
Dept NumberDept Data
A2
T1
T3
T2
USED IN A3
CUT PROCCut Proc ID
Cuttong TypeMachine Type
FIT PROCFit Proc ID
Fit Type
PAINT PROCPaint Proc IDPaint Method