database design examples 1
Download
Skip this Video
Download Presentation
Database Design Examples-1

Loading in 2 Seconds...

play fullscreen
1 / 28

Database Design Examples-1 - PowerPoint PPT Presentation


  • 143 Views
  • Uploaded on

Database Design Examples-1. 22/03/2004. 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Database Design Examples-1' - frederique


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
3 step design
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

er model
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.
slide4
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

slide5
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

Acc#

Tran#

relational model
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)

slide7
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.

er rm rules
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.

slide9
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?

example 1 3 step design sql
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.

example relational model
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#)

physical design ddl
Physical Design-DDL

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 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 Contains (

PONo NUMBER REFERENCES PurchaseOrder,

StockNo NUMBER REFERENCES Stock,

Quantity NUMBER,

Discount NUMBER,

PRIMARY KEY (PONo, StockNo)

) ;

slide13
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

) ;

dml data manipulation language
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\');

slide15
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 SQL*Loader, sqlldr command for bulk loading..

slide16
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 ;

slide17
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)> SELECT SUM(S.Price * CO.Quantity)

FROM Contains CO, Stock S

WHERE CO.PONo = 1001

AND CO.StockNo = S.StockNo

NOTE:

What if “>1” 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) > ALL(SELECT SUM(S.Price * CO.Quantity)

FROM Contains CO, Stock S

WHERE CO.PONo = 1001

AND CO.StockNo = S.StockNo)

slide18
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)

slide19
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..!!!!)

example 2 er relational algebra

area

catch

depth

distance

Overlap

Park

Lake

M

N

Pname

Lname

area

Pid

Lid

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.

relations for parking db
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)

ra operations
RA operations
  • select: <selection operation>(relation R)

retrieves the subset of rows.

  • project: <list of attributes>(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

join and natural join operations
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”

relational algebra on parking db
Relational Algebra on Parking DB
  • Find the name of the Park which contains Lake with Lid=100.

1. solution:

Pname (Park ¤  Lid=100(ParkLake))

2. solution:

Pname ( Lid=100(ParkLake ¤ Park))

3. solution:

 (t1,  Lid=100(ParkLake))

 (t2, t1 ¤Park)

Pname (t2)

slide25
cont..
  • Find the names of Parks with Lakes which have a depth of above 25.

Pname (Park ¤ (ParkLake ¤ ( depth > 25 (Lake)))

  • Find the depth of lakes that overlap with ‘I’.

depth (Lake ¤ (ParkLake ¤ ( Pname=I (Park)))

  • Find the names of Parks with at least 1 lake.

Pname (Park ¤ (ParkLake))

  • Find the names of Parks with lakes whose catch is either ‘b’ or ‘w’.

 (t1,  catch=’b’(Lake)   catch=’w’(Lake) )

Pname (Park ¤ ParkLake ¤ t1)

slide26
cont..
  • Find the names of Parks that have ‘b’ and ‘w’ as the catch in their lakes.

 (t1, Pname ( catch=’b’ (Lake)¤ ParkLake ¤ Park))

 (t2, Pname ( catch=’w’ (Lake)¤ ParkLake ¤ Park))

t1  t2

slide27
cont..
  • Find Pid of Parks that are 50 km away from the city where catch is not ‘t’.

Pid ( distance>50 (Park)) - Pid ( catch=’t’ (Lake)¤

ParkLake ¤ Park)

  • Find the names pf Parks that have at least 2 lakes.

 (t1(1Pid1,2Lid1),Pid,Lid ( ParkLake ¤ Park))

 (t2(1Pid2,2Lid2),Pid,Lid ( ParkLake ¤ Park))

 (t, t1 x t2)

Pname (Pid1=Pid2)  (Lid1 Lid2) (t)

next week 29 04 2004
NEXT WEEK, 29/04/2004
  • MORE DB DESIGN EXAMPLES

(weak entity set, N-ary relationships,

EER model)

  • SQL examples

(set operations-union,intersect,minus

set comparison operations- contains, some, all

aggregate functions-count, some, avg,max,min

group by, having,order by

delete, update operations…)

  • 1.vize: 5 nisan 2004
ad