Database design examples 1
Download
1 / 28

Database Design Examples-1 - PowerPoint PPT Presentation


  • 142 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.


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


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)


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.


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)

) ;


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');


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


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 ;


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)


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)


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)


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)


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


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