Download
1 / 12
120 likes | 234 Views
Database Model & Database Design for. Maria C. Lima Fall 2005. Company Overview. X co. is a US telehealthcare company that provides 24/7 healthcare services through its network Qualified staff of doctors and nurses available to assist patients through constant monitoring via the Internet
E N D
Database Model&Database Designfor Maria C. Lima Fall 2005
Company Overview • Xco.is a US telehealthcare company that 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 theXconetwork, so doctors and/or nurses scrutinize vital signs, blood pressure, etc.
Database Requirements • Track customers • Record customers' purchases • List products and accessories • List URLs • Report sales
Database Design 1:N M:O 1:N O:O 1:N M:O 1:N M:O 1:N M:O 1:N M:O • Surrogate Keys: • ContractNo • CustomerID • InvoiceNo • PasswordNo • SalesRepID • TeamID 1:N M:O
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
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
SQL Data Definition Language - DDL • CREATE TABLE statements to create relations. As an example, CREATE TABLE CUSTOMER( CustomerID int NOT NULL (500,1), Name char(30) NOT NULL, Address char(60) NULL, City char(30) NULL, State char(2) NULL, Zip char(10) NULL, Phone char(10) NULL, Email char(30) NOT NULL, PasswordNo int 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);
SQL Data Manipulation Language - DML • UPDATE command: UPDATE CUSTOMER SET City = ‘Bronx’ WHERE CustomerID = 284; • Bulk UPDATE: UPDATE CUSTOMER SET Zip = ‘10522’ WHERE City = ‘Purchase’;
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;
SQL Data Manipulation Language - DML • Find URL Addresses for customers in Ohio SELECT URLAddress FROM NETWORK As N, CUSTOMER AS C WHERE N.CustomerID = C.CustomerID AND C.State = ‘Ohio’; • Find TeamID, doctor and nurse for customers in New York SELECT TeamID, DoctorName, NurseName FROM HEALTHCARE_STAFF As H, NETWORK As N, CUSTOMER AS C WHERE H.TeamID = N.TeamID AND N.CustomerID = C.CustomerID AND C.State = ‘New York’;
