Transcript Document

DirtBike Order Database– ER
Diagram
Prepared by
(Reda Alabdrabalnabi)
Fall 2012
Designing Databases
with Entity Relationship Diagrams
Step 1: Define Entity Classes and Primary Keys
Step 2: Define Relationships Among Entity Classes
Step 3: Define Fields for each Relationship
Step 4: Create Database
Step 1: Define Entity Classes and Primary Keys
Entity Class
Primary Key
Customer
CustomerId
Order
OrderID
Product
ProductId
Distributor
DistId
Business Rules
Each customer can have one order
Each customer can have multiple order
Customers can be in your database and not have any orders
Each order is made by a single customer
Orders may have multiple products
Each product has to have a distributor
Orders cannot be blank but have at least one product
Product can be blank but have at least one product
Products can be listed in your database but not purchased
The same product can be purchased through many orders
A distributor can supply multiple products
A distributor has to supply at least one product
E-R Diagrams
Use 5 symbols
1.
2.
3.
4.
5.
Rectangle – entity class
Dotted line – relationship
| - single relationship
0 – zero/optional relationship
Crow’s foot () – multiple relationship
Step 2: Define Relationships Among Entity Classes
CUSTOMER
ORDER
Detailed Orders
DISTRIBUTOR
PRODUCT
Step 3: Define Fields for each Relationship
Customer
CustomerID
LastName
FirstName
Address
City
State
Zip
Phone
MSRP
Ignition
Weight
QOH
Order
OrderId
CustomerId
Product
ProductId
ProductName
Manufacturer
Distributor
DistributorID
Distributor
Detailed Orders
OrderID
ProductId
Quantity
Color
DistID
Step 4: Create Database
(At this point we will begin working with our DBMS system, which will be
Microsoft Access)