database model database design for n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Database Model & Database Design for PowerPoint Presentation
Download Presentation
Database Model & Database Design for

Loading in 2 Seconds...

play fullscreen
1 / 12

Database Model & Database Design for - PowerPoint PPT Presentation


  • 86 Views
  • Uploaded on

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

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 Model & Database Design for' - gelsey


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
database model database design for

Database Model&Database Designfor

Maria C. Lima

Fall 2005

company overview
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
Database Requirements
  • Track customers
  • Record customers' purchases
  • List products and accessories
  • List URLs
  • Report sales
database design
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
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
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
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
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 dml1
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 dml2
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’;