Database Design Examples-1

1 / 28

About This Presentation

Title:

Database Design Examples-1

Description:

Database Design Examples-1 22/03/2004 3 step design Conceptual Design Highest level design Issues: data types, relationships, constraints Uses ER model ... – PowerPoint PPT presentation

Number of Views:103

Avg rating:3.0/5.0

Slides: 29

Provided by: yildizEd

more less

Transcript and Presenter's Notes

Title: Database Design Examples-1

1
Database Design Examples-1

22/03/2004

2
3 step design

Conceptual Design
Highest level design
Issues data types, relationships,
constraints
Uses ER model
Logical Design
Implementation of conceptual model
3 ways hierarchical, network, relational
Apply relational model
Uses RA (relational algebra) as a formal
query language
Physical Design
Actual computer implementation
Issues mem. manag., storage, indexing

3
ER model

The most popular conceptual data modeling
technique. (Give an example of other conceptual
design tool?)
Integrates with relational model.
The scenario is partitioned into entities which
are characterized with attributes and
interrelated via relationships. Entity set is
a set of entities of the same type.
Entity is an independent conceptual existence in
the scenario.
An attribute (or a set of attributes) that
uniquely identifies instance of an entity is
called the key.
1-) Super key 2-) candidate key (minimal
superkey) 3-) primary key (candidate key
chosen by DBA)
An attribute can be single-valued or multi-valued.

4
cont..

At least 2 entities participate in a
relationship.
(give an example of recursive relationship)
Binary relationship, ternary relationship
(give an example of ternary relationship)
Cardinality constraints 1-1, 1-N, N-M
Relationships may have their own attributes
Example of an ER diagram

R
E1
E2
1
N
d
m1
a3
a1
Can we migrate a3? IF we can, to where?
a2
5
cont..

Weak Entity set An entity set that does not have
enough attributes to form a primary key.
Think of Transaction entity set(transaction,
date, amount) assuming that different accounts
might have similar transactions.
We need a strong entity set (owner set) in
order to distinguish the entities of weak entity
set.
Question Is there a way to represent this kind
of scenario without using another entity set?

date
R
account
transaction
1
N
amount
Tran
Acc
6
Relational Model

Data representation model introduced by Codd,
1970.
E-R RM
Relation ? Table
Attributes ? Columns
Table is an unordered collection of tuples(rows).
Degree of a relation is the of columns.
Data types of attributes DOMAINS
int, float, character,date, large_object
(lob), user-defined data types(only for ORDBs)

7
cont..

Logical consistency of data is ensured by
certain constraints
key every relation must have PK key
entity integrity no PK can be NULL
referential integrity value of attributes
of the foreign key either must appear as a value
in PK of another table (or the same table, give
an example) or must be null.
Definitions
PrimaryKey is chosen among the candidate key
by DBA.
ForeignKey is set of attributes in a relation
which is duplicated in another relation.

8
ER?RM Rules

S/w packages (CASE tools) such as Erwin, Oracle
Designer 2000, Rational Rose can translate ER to
RM.
4 steps for transformation
1.) Map each entity set in ER into a separate
table in RM
(Also, map the attributes, and PK)
2.) Weak entity set with attributes (a1,..an)
and owner set attributes (b1,b2,..bm) MAP it to
a table with (a1,..an) U (b1,b2,..bm)
attributes. (b1,b2,..bm) becomes the foreign key.
(a1,..an) U discriminator becomes the PK.

9
cont..

3.) Binary Relationship S between R1 and R2
entity sets. Assume (a1,a2,an) is the attributes
of S.
If cardinality is 1-1 Chose either
relations( say S)
and extend it with PK(T) U
(a1,a2,an)
If cardinality is 1-N Chose N-side
relation( say S)
and extend it with PK(T) U
(a1,a2,an)
If cardinality is N-M Represent it with a
new
relation with PK(T) U PK(S) U
(a1,a2,an)
4.) Multivalued Attribute A of entity set R is
represented with a new relation with A U PK(S).
What is the PK of new table?

10
Example 1- (3-step design,SQL)

DB of a Managing customer orders
Scenario
a customer has a unique customer number and
contact information
a customer can place many orders, but a given
purchase order is placed by one customer
a purchase order has a many-to-many
relationship with a stock item.
Here is the ER diagram.

11
Example-(Relational model)
CUSTOMER
PURCHASE_ORDER
PK is (PurchaseOrder) FK is(Cust)
Corresponds to 1-N relationship
CUST_PHONES
STOCK_ITEMS
CONTAINS
PK is (Cust, Phones)
Corresponds to N-M relationship
PK is (PurchaseOrder, Stock)
12
Physical Design-DDL

CREATE TABLE Customer (
CustNo NUMBER NOT NULL,
CustName VARCHAR2(200) NOT NULL,
Street VARCHAR2(200) NOT NULL,
City VARCHAR2(200) NOT NULL,
State CHAR(2) NOT NULL,
Zip VARCHAR2(20) NOT NULL,
PRIMARY KEY (CustNo)
)

CREATE TABLE PurchaseOrder ( PONo
NUMBER, / purchase order no / Custno
NUMBER REFERENCES Customer,
/ Foreign KEY referencing customer /
OrderDate DATE, / date of order /
ShipDate DATE, / date to be shipped /
ToStreet VARCHAR2(200), / shipto address /
ToCity VARCHAR2(200), ToState
CHAR(2), ToZip VARCHAR2(20),
PRIMARY KEY(PONo) )
CREATE TABLE Contains ( PONo NUMBER REFERENCES
PurchaseOrder, StockNo NUMBER
REFERENCES Stock, Quantity NUMBER,
Discount NUMBER, PRIMARY KEY
(PONo, StockNo) )
13
cont..

CREATE TABLE Cust_Phones (
CustNo NUMBER REFERENCES Customer,
Phones VARCHAR2(20),
PRIMARY KEY (CustNo, Phones)
)

CREATE TABLE Stock ( StockNo NUMBER PRIMARY
KEY, Price NUMBER, TaxRate NUMBER )
14
DML (data manipulation language)

INSERT INTO Stock VALUES(1004, 6750.00, 2)
INSERT INTO Stock VALUES(1011, 4500.23, 2)
INSERT INTO Stock VALUES(1534, 2234.00, 2)
INSERT INTO Stock VALUES(1535, 3456.23, 2)
INSERT INTO Customer VALUES (1, 'Jean Nance', '2
Avocet Drive', 'Redwood Shores', 'CA', '95054')
INSERT INTO Customer VALUES (2, 'John Nike', '323
College Drive', 'Edison', 'NJ', '08820')
INSERT INTO Cust_Phones (1, '415-555-1212)
INSERT INTO Cust_Phones (2, '609-555-1212')
INSERT INTO Cust_Phones (2, '201-555-1212')

15
cont..

INSERT INTO PurchaseOrder VALUES (1001, 1,
SYSDATE, '10-MAY-1997',NULL, NULL, NULL, NULL)
INSERT INTO PurchaseOrder VALUES (2001, 2,
SYSDATE, '20 MAY-1997', '55 Madison Ave',
'Madison', 'WI', '53715')
INSERT INTO Contains VALUES( 1001, 1534, 12, 0)
INSERT INTO Contains VALUES(1001, 1535, 10, 10)
INSERT INTO Contains VALUES(2001, 1004, 1, 0)
INSERT INTO Contains VALUES(2001, 1011, 2, 1)
NOTE
You can use bulk loading if the DB has this
functionality. Example Oracle has SQLLoader,
sqlldr command for bulk loading..

16
SQL

Q1 Get Customer and Data Item Information for a
Specific Purchase Order
SELECT C.CustNo, C.CustName, C.Street,
C.City, C.State, C.Zip,
P.PONo, P.OrderDate,
CO.StockNo, CO.Quantity,
CO.Discount
FROM Customer C, PurchaseOrder P,
Contains CO
WHERE C.CustNo P.CustNo
AND P.PONo CO.PONo
AND P.PONo 1001
Q2 Get the Total Value of Purchase Orders
SELECT P.PONo, SUM(S.Price
CO.Quantity)
FROM PurchaseOrder P, Contains
CO, Stock S
WHERE P.PONo CO.PONo
AND CO.StockNo
S.StockNo
GROUP BY P.PONo

17
SQL

Q3 List the Purchase Orders whose total value is
greater than that of a specific Purchase Order.
SELECT P.PONo
FROM PurchaseOrder P, Contains
CO, Stock S
WHERE P.PONo CO.PONo
AND CO.StockNo
S.StockNo
AND SUM(S.Price
CO.Quantity) gt SELECT SUM(S.Price
CO.Quantity)
FROM Contains CO, Stock S
WHERE CO.PONo 1001
AND CO.StockNo S.StockNo
NOTE
What if gt1 customers can have the
same PurchaseOrderNumber? ? Use ANY for a
general solution..
SELECT P.PONo
FROM PurchaseOrder P, Contains CO,
Stock S
WHERE P.PONo CO.PONo
AND CO.StockNo
S.StockNo
GROUP BY P.PONo
HAVING SUM(S.Price CO.Quantity) gt
ALL(SELECT SUM(S.Price CO.Quantity)
FROM Contains CO, Stock S

18
SQL

Q4 Find the Purchase Order that has the maximum
total value.
CREATE VIEW X(Purchase,Total) AS
SELECT P.PONo, SUM(S.Price
CO.Quantity)
FROM PurchaseOrder P, Contains
CO, Stock S
WHERE P.PONo CO.PONo
AND CO.StockNo
S.StockNo
GROUP BY P.PONO
---------------------------
SELECT P.PONo
FROM X
GROUP BY P.PONo
HAVING Total ( SELECT max(Total)
FROM X)

19
DML

Delete Purchase Order 1001
DELETE
FROM Contains
WHERE PONo 1001
DELETE
FROM PurchaseOrder
WHERE PONo 1001
(Important The order of commands is
important..!!!!)
Delete the database.
drop table Cust_Phones
drop table Contains
drop table Stock
drop table PurchaseOrder
drop table Customer
(Important The order of commands is
important..!!!!)

20
Example-2 (ER, relational algebra)

Scenario for Parking database
?We want to develop a database that has parks
and lakes that are overlapping with each other.
Overlapping area is also stored.
? Parks have their name, area, distance and a
unique id.
? Lakes have name, depth, catch and a unique
id.

21
Relations for Parking DB
PARK
LAKE
PARK_LAKE
RA (relational algebra operations)
RA is a formal query language and the core of
SQL. Not implemented in commercial DBs. RA
consists of a set of operands (tables) and
operations (select, project, union,cross-product,
difference, intersection)
22
RA operations

select ?ltselection operationgt(relation R)
retrieves the subset of rows.
project ?ltlist of attributesgt (relation R)
retrieves the subset of columns.
Assume R and S are tables
union R ? S, all tuples that are R OR S
intersect R ? S, all tuples that are both R
AND S
difference R - S, all tuples that are in R but
not in S
cross-product R x S, all attributes of R
followed by those of S

Requires compatibility
23
Join and natural join operations

A derived operation.
Is the cross-product followed by a select.
R c S ?c (R x S)
c the condition that usually refers to the
attributes of both R and S.
If c is an equality condition and consists of one
column (the common column), then it is called
natural join. (R S)
For complex queries, use the renaming operation,
?
?(newname(1?attr1), oldname) means that
the relation oldname becomes the
newname. Also the first attribute of newname
table is called the attr1

24
Relational Algebra on Parking DB