Transcript Surrogate Keys - Web SystemsView

Database Model
&
Database Design
for
Maria C. Lima
Fall 2005
Company Overview
 X is a US telehealthcare company that
co.
provides
– 24/7 healthcare services through its network
 Qualified staff of doctors and nurses available to
assist patients through constant monitoring via the
Internet
– healthcare solutions for patients with chronic
diseases
 products are imported from Chinese Yco.
– X120 – monitor
– X150 – ultimate monitor
– respective accessories
 patients install the machine(s) in their homes and
connect them to the X network, so doctors and/or
nurses scrutinize vital signs, blood pressure, etc.
co
December 2005
2
Database Requirements





Track customers
Record customers' purchases
List products and accessories
List URLs
Report sales
December 2005
3
Database Model
CONTRACT
SALES_REP
ITEM
ContractNo
SalesRepID
ItemNo
ContractDate
ContractExpDate
SalesRepName
ItemName
UnitSalesPrice
CUSTOMER
CustomerID
Name
Address
City
State
Zip
Phone
Email
INVOICE
InvoiceNo
PurchaseOrderNo
DateIssued
Subtotal
Tax
Total
DatePaid
HEALTHCARE_STAFF
NETWORK
TeamID
PasswordNo
DoctorID
DoctorName
NurseID
NurseName
URLAddress
WebAdministrator
December 2005
INVOICE_LINE_ITEM
LineNo
InvoiceNo
Quantity
UnitSalesPrice
ExtendedPrice
4
Database Design
CONTRACT
SALES_REP
ContractNo
ContractDate
ContractExpDate
CustomerID (FK)
ITEM
SalesRepID
ItemNo
SalesRepName
1:N
M:O
CUSTOMER
1:N
O:O
InvoiceNo
Name
Address
City
State
1:N
Zip
M:O
Phone
Email (AK 1.1)
PasswordNo (FK)
PurchaseOrderNo
DateIssued
Subtotal
Tax
Total
DatePaid
CustomerID (FK)
SalesRepID (FK)
1:N
M:O
HEALTHCARE_STAFF
NETWORK
TeamID
December 2005
1:N
M:O
INVOICE
CustomerID
DoctorID
DoctorName
NurseID
NurseName
ItemName
UnitSalesPrice
PasswordNo
1:N
M:O
URLAddress
WebAdministrator
CustomerID (FK)
TeamID (FK)
INVOICE_LINE_ITEM
LineNo
InvoiceNo (FK)
1:N
M:O
Quantity
UnitSalesPrice
ExtendedPrice
ItemNo (FK)
Surrogate Keys:
• ContractNo
• CustomerID
• InvoiceNo
• PasswordNo
• SalesRepID
• TeamID
5
Max and Min Cardinality


Maximum Cardinality - 1:N
Minimum Cardinality
– O:O
 no enforcement action needs to be taken
– M:O
 Parent-required actions
– Rows on parent can be created
– Cascade updates if the primary key is not a surrogate key
– Decision to be made: if parent is deleted, do we delete the child?
» If YES — cascade deletes
» If NO — prohibit
 Child-required actions
– are easily enforced by defining referential integrity constraints and
making foreign keys NOT NULL
– Child can be deleted
December 2005
6
Referential Integrity Constraints

CONTRACT.CustomerID must exist in CUSTOMER.CustomerID
SELECT
CustomerID
FROM
CONTRACT
WHERE
CustomerID NOT IN
(SELECT
CustomerID
FROM
CUSTOMER
WHERE
CUSTOMER.CustomerID = CONTRACT.CustomerID);







NETWORK.CustomerID must exist in CUSTOMER.CustomerID
NETWORK.TeamID must exist in HEALTHCARE_Staff.TeamID
CUSTOMER.PasswordNo must exist NETWORK.PasswordNo
INVOICE.CustomerID must exist in CUSTOMER.CustomerID
INVOICE.SalesRepID must exist in SALES_REP.SalesRepID
INVOICE_LINE_ITEM.InvoiceNo must exist in INVOICE.InvoiceNo
INVOICE_LINE_ITEM.ItemNo must exist in ITEM.ItemNo
December 2005
7
SQL Data Definition Language - DDL

CREATE TABLE statements to create relations. As an example,
CREATE TABLE CUSTOMER(
CustomerID
int
Name
char(30)
Address
char(60)
City
char(30)
State
char(2)
Zip
char(10)
Phone
char(10)
Email
char(30)
PasswordNo
int
NOT NULL (500,1),
NOT NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NOT NULL,
NOT NULL (1000,25),
CONSTRAINT
CPK
PRIMARY KEY (CustomerID),
CONSTRAINT
CAK1
UNIQUE (Email),
CONSTRAINT
CFK
FOREIGN KEY (PasswordNo),
REFERENCES NETWORK (PasswordNo)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
December 2005
8
SQL Data Manipulation Language - DML
 UPDATE command:
UPDATE
CUSTOMER
SET
WHERE
City = ‘Bronx’
CustomerID = 284;
 Bulk UPDATE:
December 2005
UPDATE
CUSTOMER
SET
WHERE
Zip = ‘10522’
City = ‘Purchase’;
9
SQL Data Manipulation Language - DML
 Find customers in Florida and Georgia
SELECT Name
FROM
CUSTOMER AS C
WHERE C.State = ‘Florida’
OR C.State = ‘Georgia’;
 Find total purchases, including taxes, for
CustomerID1098 after January 2005 inclusive
SELECT SUM (Total) AS Sales
FROM
INVOICE AS I
WHERE I.CustomerID = ‘1098’
AND I.DatePaid >= 12/01/05;
December 2005
10
SQL Data Manipulation Language - DML
 Find URL Addresses for customers in Ohio
SELECT
FROM
WHERE
AND
URLAddress
NETWORK As N, CUSTOMER AS C
N.CustomerID = C.CustomerID
C.State = ‘Ohio’;
 Find TeamID, doctor and nurse for customers in New York
SELECT
FROM
WHERE
AND
AND
December 2005
TeamID, DoctorName, NurseName
HEALTHCARE_STAFF As H, NETWORK As N, CUSTOMER AS C
H.TeamID = N.TeamID
N.CustomerID = C.CustomerID
C.State = ‘New York’;
11
Questions 
