Scribd
Upload
7 views39 pages

Lecture5 SQL1

The document covers the relational data model and SQL, detailing the phases of the software development life cycle (SDLC) and the structure of databases. It explains the concepts of primary and foreign keys, integrity constraints, and the use of SQL for database management, including data definition, manipulation, and control. Additionally, it provides examples of entity-relationship diagrams (ERDs) and SQL commands for creating tables in a database.

Uploaded by

herman
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
7 views39 pages

Lecture5 SQL1

The document covers the relational data model and SQL, detailing the phases of the software development life cycle (SDLC) and the structure of databases. It explains the concepts of primary and foreign keys, integrity constraints, and the use of SQL for database management, including data definition, manipulation, and control. Additionally, it provides examples of entity-relationship diagrams (ERDs) and SQL commands for creating tables in a database.

Uploaded by

herman
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 39

LECTURE 5 :

RELATIONAL DATA MODEL AND


STRUCTURED QUERY LANGUAGE (SQL) I

1
Overview
SDLC phases
Planning Analysis Design Implementation Maintenance

Logical Physical Data


Database Database Database and database
database database
fundamentals analysis implementation administration
design design and security

• database • entity- • Relational • SQL • SQL • SQL


environment relationship data model • technical • data security
and (ER) diagram • transforming specifications • database backup
development • enhanced ER ER diagram of the database and recovery
process diagram into relations
• normalization

2
RELATIONAL DATA MODEL

3
Business rule: Each patient has one to many patient history.

Each patient history is belonged to one and only one patient.

ERD: PATIENT
P_ID PATIENT_HISTORY
P_Name PH_ID
(P_Lastname, P_Firstname) has PH_Date
P_DOB PH_Symptom_Description
[P_Age] PH_Diagnosis
P_Remarks PH_Status

4
ERD: PATIENT
P_ID PATIENT_HISTORY
P_Name PH_ID
(P_Lastname, P_Firstname) contains PH_Date
P_DOB PH_Symptom_Desc
[P_Age] PH_Diagnosis
P_Remarks PH_Status

A relational data model


Relational Data Model: PATIENT consists of relations
P_ID
____ P_Lastname P_Firstname P_DOB P_Remarks

PATIENT_HISTORY

PH_ID
______ PH_Date PH_Symptom_Desc PH_Diagnosis PH_Status P_ID

5
Relational Data Model: PATIENT
P_ID
_____ P_Lastname P_Firstname P_DOB P_Remarks

PATIENT_HISTORY

PH_ID
_______ PH_Date PH_Symptom_Desc PH_Diagnosis PH_Status P_ID
----------

Tables in the PATIENT


A two-dimensional table
Relational Database: P_ID P_Lastname P_Firstname P_DOB P_Remarks
____
001 Mark Lui 30-APR-1998 <Null>

Each row is a record 002 Sarah Wong 6-AUG-1988 Allergic to nuts


Each column is an
003 Elsie Lee 24-OCT-1993 Serious asthma attribute

PATIENT_HISTORY
PH_ID
____ PH_Date PH_Symptom_Desc PH_Diagnosis PH_Status P_ID
4011 5-AUG-2020 Sore throat, fever 101F, headache Flu 3d medication 001

4012 7-AUG-2020 Fatigue headache and muscle ache URI 2d medication w/ 002
antibiotics
4013 9-AUG-2020 Fever 102F, serious coughing, nausea Possibly Refer to A&E 002
pneumonia

6
Keys (1) - Primary Key
PATIENT PATIENT_HISTORY
P_ID PH_ID
P_Name PH_Date
(P_Lastname, P_Firstname) PH_Symptom_Desc
P_DOB contains
PH_Diagnosis
[P_Age] PH_Status
P_Remarks

Primary key is an attribute PATIENT


(or combination of attributes) that P_ID P_Lastname P_Firstname P_DOB P_Remarks
____
uniquely identifies every row in a relation
• can be simple or composite
PATIENT
____
P_ID P_Lastname P_Firstname P_DOB P_Remarks
001 Mark Lui 30-APR-1998 <Null>

002 Sarah Wong 6-AUG-1988 Allergic to nuts

003 Elsie Lee 24-OCT-1993 Serious asthma

7
Primary Key in relations
PATIENT PATIENT_HISTORY
P_ID PH_ID
P_Name PH_Date
(P_Lastname, P_Firstname) PH_Symptom_Desc
P_DOB contains PH_Diagnosis
[P_Age] PH_Status
P_Remarks

PATIENT_HISTORY

PH_ID
______ PH_Date PH_Symptom_Desc PH_Diagnosis PH_Status P_ID

PATIENT_HISTORY
____
PH_ID PH_Date PH_Symptom_Desc PH_Diagnosis PH_Status P_ID
4011 5-AUG-2020 sore throat, fever 101F, headache Flu 3d medication 001

4012 7-AUG-2020 Fatigue headache and muscle ache URI 2d medication w/ 002
antibiotics
4013 9-AUG-2020 Fever 102F, serious coughing, nausea Possibly Refer to A&E 002
pneumonia

8
Other examples of Primary Key ?

Student Employee
ID ID

HKID

9
Keys (2) - Foreign Key
PATIENT PATIENT_HISTORY
P_ID PH_ID
P_Name PH_Date
(P_Lastname, P_Firstname) PH_Symptom_Desc
P_DOB contains
PH_Diagnosis
[P_Age] PH_Status
P_Remarks

PATIENT

P_ID
____ P_Lastname P_Firstname P_DOB P_Remarks

PATIENT_HISTORY Foreign key is an attribute


PH_ID PH_Date PH_Symptom_Desc PH_Diagnosis PH_Status (or combination of attributes) that
______ P_ID
references the primary key of
same/another relation
• can be simple or composite

10
Foreign Key in relations
PATIENT
____
P_ID P_Lastname P_Firstname P_DOB P_Remarks
001 Mark Lui 30-APR-1998 <Null> ‘P_ID’ is Foreign Key,
002 Sarah Wong 6-AUG-1988 Allergic to nuts matches Primary Key of
‘PATIENT’
003 Elsie Lee 24-OCT-1993 Serious asthma

PATIENT_HISTORY
____
PH_ID PH_Date PH_Symptom_Desc PH_Diagnosis PH_Status P_ID
4011 5-AUG-2020 sore throat, fever 101F, headache Flu 3d medication 001

4012 7-AUG-2020 Fatigue headache and muscle ache URI 2d medication w/ 002
antibiotics
4013 9-AUG-2020 Fever 102F, serious coughing, nausea Possibly Refer to A&E 002
pneumonia

Which patient was referred to A&E after the diagnosis?


Who is P_ID 002?
11
ERD of the Travel Agency – 3rd Year
STAFF
S_ID
S_Name
DEPARMENT EmploymentDate
D_ID Consists_of Salary
D_Name

Has
Is_entitled_to

BENEFIT FAMILY
B_CODE Is_offered_to Name
B_DESC Birthdate

12
ERD:

Relational Data Model:

DEPARTMENT STAFF

D_ID D_Name S_ID


______ D_Name EmploymentDate Salary D_ID B_CODE
____

BENEFIT FAMILY
B_CODE
_________ B_DESC S_ID
____ Name
____ Birthdate B_CODE

Composite primary key

13
RELATIONAL DATA MODEL:
INTEGRITY CONSTRAINTS

14
Integrity Constraints
• Relations comply with integrity constraints that facilitate maintaining
the accuracy and integrity of data

• Domain Constraint
• Entity Integrity Constraint
• Referential Integrity Constraint

15
1. Domain Constraint
allowable values for an attribute
e.g. data type and size

PATIENT PATIENT_HISTORY
P_ID P_Lastname P_Firstname P_DOB P_Remarks PH_ID PH_Date PH_Symptom_ PH_Diagnosis PH_Status P_ID
Desc

Fields Data type and size Fields Data type and size
P_ID VARCHAR2 (3) PH_ID VARCHAR2(4)
P_Lastname VARCHAR2 (100) PH_Date DATE

P_Firstname VARCHAR2 (100) PH_Symptom_Desc VARCHAR2 (300)

P_DOB DATE PH_Diagnosis VARCHAR2 (300)

P_Remarks VARCHAR2 (1000) PH_Status VARCHAR2 (15)

No need to specify P_ID VARCHAR2 (3)

size for DATE

16
Common Data Types in Oracle
Data Type Description

VARCHAR2 (size) For storing characters. Example: 'HKUST'

Note: Similar to String data type in VBA, Java and Python


NUMBER For storing numerical values, including integers and
(precision, scale) floating-point values.

NUMBER(5,2) for storing the number 123.45


NUMBER(5,0) for storing integers such as 10000
DATE For storing both date and/or time.
Example: '20-SEP-2019'

17
2. Entity Integrity Constraint
Primary key attribute must NOT be null

DEPARTMENT STAFF
D_ID Consists_of S_ID
D_Name S_Name

DEPARTMENT STAFF

D_ID D_Name S_ID


____ S_Name D_ID

Fields Null? / Not Null?


Fields Null? / Not Null?
S_ID Primary Key field – Not Null
D_ID Primary Key field – Not Null
S_Name Not Null
D_Name Not Null
D_ID Foreign Key – Allow null values

18
DEPARTMENT STAFF
D_ID Consists_of S_ID
D_Name S_Name

DEPARTMENT STAFF

D_ID D_Name S_ID


____ S_Name D_ID
____

001 Peter Li <Null>


A Development
002 Mary Chan A
003 Anna Lee A
B Marketing 004 Tom Lee B
005 John Lam B

19
3. Referential Integrity Constraint
Foreign key has two possibilities:
• The foreign key value could be null
• The foreign key value must match a primary key value

DEPARTMENT Consists_of STAFF


D_ID S_ID
D_Name S_Name

DEPARTMENT STAFF
D_ID
____ D_Name S_ID
____ S_Name D_ID

001 Peter Li <Null>


A Development Invalid! E doesn’t
002 Mary Chan A
exist in
003 Anna Lee A
DEPARTMENT!!
B Marketing 004 Tom Lee B
005 John Lam B
006 Chris Wong E 20
3. Referential Integrity Constraint
Foreign key has two possibilities:
• The foreign key value could be null
• The foreign key value must match a primary key value
DEPARTMENT

D_ID
____ D_Name

BENEFIT Referential integrity constraints


B_Code
_________ B_Desc are drawn via arrows from the
foreign keys to the
FAMILY corresponding primary keys.
S_ID
____ Name Birthdate
______ B_Code

STAFF
S_ID
_____ S_Name EmploymentDate Salary B_Code D_ID

21
Sample Data for the Travel Agency in
Oracle database

STAFF

DEPARTMENT BENEFIT
FAMILY
22
INTRODUCTION TO SQL

23
What is Structured Query Language (SQL)?
• The standard language for relational database management systems
(RDBMS)
• Latest revision: SQL:2019
• Core and other parts
• Most RDBMS are at least in partial compliance with
the SQL standard

• Each vendor’s version also includes enhancements features, and


capabilities beyond the core SQL standard

24
Benefits of a Standardized Relational Language
• Application longevity
• a standard language tends to exist for a long time
• Reduced dependence on a single vendor
• can use different vendors for the DBMS
• Cross-system communication
• different DBMSs and programs can communicate and cooperate
• Application portability
• applications can be moved from machine to machine

25
Types of SQL Commands
SQL Types Syntax When do we use these commands?
Physical Database Design
CREATE • Define structure of tables in a database
Data Definition Language
ALTER
(DDL) Maintenance
DROP
• Modify structure of tables after the database is implemented
INSERT
Data Manipulation Language UPDATE Implementation
(DML) DELETE • Access and manipulate the data in the database
SELECT
Physical Database Design
• Control a database, including managing users’ privileges
Data Control Language GRANT
(DCL) REVOKE Maintenance
• Modify the privileges after the database is implemented

26
DATA DEFINITION LANGUAGE (DDL):
CREATE TABLE

27
Create tables in a database
PATIENT
P_ID
____ P_Lastname P_Firstname P_DOB P_Remarks

CREATE TABLE PATIENT


(
P_ID VARCHAR2 (5) NOT NULL,
P_LASTNAME VARCHAR2 (200) NOT NULL,
P_FIRSTNAME VARCHAR2 (200) NOT NULL,
P_DOB DATE NOT NULL,
P_REMARKS VARCHAR2 (2000),
CONSTRAINT PATIENT_PK PRIMARY KEY(P_ID)
);

28
PATIENT_HISTORY

PH_ID
______ PH_Date PH_Symptom_Desc PH_Diagnosis PH_Status P_ID

CREATE TABLE PATIENT_HISTORY


(PH_ID VARCHAR2(5) NOT NULL,
PH_DATE DATE DEFAULT SYSDATE,
PH_SYMPTOM_DESC VARCHAR2(1000),
PH_DIAGNOSIS VARCHAR2(1000),
PH_STATUS VARCHAR2(16),
P_ID VARCHAR2(5) NOT NULL,
CONSTRAINT PATIENT_HISTORY_PK PRIMARY KEY(PH_ID),
CONSTRAINT PATIENT_HISTORY_FK FOREIGN KEY(P_ID) REFERENCES PATIENT(P_ID),
CONSTRAINT PH_STATUS_CHECK CHECK (PH_STATUS IN (‘2d Medicine’,’3d
Medicine’,‘Refer to AE’,
‘Physio Treatment’,’No action taken’)));

29
PATIENT_HISTORY

PH_ID
______ PH_Date PH_Symptom_Desc PH_Diagnosis PH_Status P_ID

CREATE TABLE PATIENT_HISTORY


(PH_ID VARCHAR2(5) NOT NULL,
PH_DATE DATE DEFAULT SYSDATE,
PH_SYMPTOM_DESC VARCHAR2(1000),
PH_DIAGNOSIS VARCHAR2(1000),
PH_STATUS VARCHAR2(16),
P_ID VARCHAR2(5) NOT NULL,
CONSTRAINT PATIENT_HISTORY_PK PRIMARY KEY(PH_ID),
CONSTRAINT PATIENT_HISTORY_FK FOREIGN KEY(P_ID) REFERENCES PATIENT(P_ID),
CONSTRAINT PH_STATUS_CHECK CHECK (PH_STATUS IN (‘2d Medicine’,’3d
Medicine’,‘Refer to AE’,
‘Physio Treatment’,’No action taken’)));

30
PATIENT_HISTORY

PH_ID
______ PH_Date PH_Symptom_Desc PH_Diagnosis PH_Status P_ID

CREATE TABLE PATIENT_HISTORY


(PH_ID VARCHAR2(5) NOT NULL,
PH_DATE DATE DEFAULT SYSDATE,
PH_SYMPTOM_DESC VARCHAR2(1000),
PH_DIAGNOSIS VARCHAR2(1000),
PH_STATUS VARCHAR2(16),
P_ID VARCHAR2(5) NOT NULL,
CONSTRAINT PATIENT_HISTORY_PK PRIMARY KEY(PH_ID),
CONSTRAINT PATIENT_HISTORY_FK FOREIGN KEY(P_ID) REFERENCES PATIENT(P_ID),
CONSTRAINT PH_STATUS_CHECK CHECK (PH_STATUS IN (‘2d Medicine’,’3d
Medicine’,‘Refer to AE’,
‘Physio Treatment’,’No action taken’)));

31
PATIENT_HISTORY

PH_ID
______ PH_Date PH_Symptom_Desc PH_Diagnosis PH_Status P_ID

CREATE TABLE PATIENT_HISTORY


(PH_ID VARCHAR2(5) NOT NULL,
PH_DATE DATE DEFAULT SYSDATE,
PH_SYMPTOM_DESC VARCHAR2(1000),
PH_DIAGNOSIS VARCHAR2(1000),
PH_STATUS VARCHAR2(16),
P_ID VARCHAR2(5) NOT NULL,
CONSTRAINT PATIENT_HISTORY_PK PRIMARY KEY(PH_ID),
CONSTRAINT PATIENT_HISTORY_FK FOREIGN KEY(P_ID) REFERENCES PATIENT(P_ID),
CONSTRAINT PH_STATUS_CHECK CHECK (PH_STATUS IN (‘2d Medicine’,’3d
Medicine’,‘Refer to AE’,
‘Physio Treatment’,’No action taken’)));

32
Summary: CREATE TABLE command

• Define columns and constraints


• Appropriate datatype (VARCHAR2, NUMBER, DATE)
• Columns that should not accept null values (NOT NULL)
• Columns that are the primary keys (PRIMARY KEY)
• Columns that are the foreign keys (FOREIGN KEY)
• Pair of foreign key and primary key (REFERENCES)
• Columns with default values (DEFAULT)
• Columns with check (CHECK)

33
STAFF
S_ID
______ D_Name EmploymentDate Salary D_ID B_CODE

CREATE TABLE STAFF


(S_ID VARCHAR2(5) NOT NULL,
D_Name VARCHAR2(100) NOT NULL,
EmploymentDate DATE DEFAULT SYSDATE,
Salary NUMBER (8, 2) NOT NULL,
D_ID VARCHAR2 (5) NOT NULL,
No need here

B_CODE VARCHAR2 (5) NOT NULL,


No need here
CONSTRAINT STAFF_PK PRIMARY KEY (S_ID),
CONSTRAINT STAFF_FK1 FOREIGN KEY(D_ID) REFERENCES DEPARTMENT(D_ID),
CONSTRAINT STAFF_FK2 FOREIGN KEY(B_CODE) REFERENCES BENEFIT(B_CODE));

Can you create STAFF, without creating DEPARTMENT and BENEFIT in a database? 34
35
DATA DEFINITION LANGUAGE (DDL):
ALTER TABLE & DROP TABLE

36
Changing Table Definitions
PATIENT
P_ID P_Lastname
____ P_Firstname P_DOB P_Remarks P_
NICKNAME
PATIENT_HISTORY

PH_ID
______ PH_Date PH_Symptom_Desc PH_Diagnosis PH_Status P_ID

• Add ‘P_Nickanme’ column into the PATIENT table


ALTER TABLE PATIENT
ADD P_NICKNAME VARCHAR2 (200);

• Change the data type and size of ‘PH_Daignosis’ column of the PATIENT_HISTORY
table to VARCHAR2 (300)
ALTER TABLE PATIENT_HISTORY
MODIFY PH_DIAGNOSIS VARCHAR2 (300);

37
Changing Table Definitions
PATIENT
P_ID P_Lastname P_Firstname P_DOB P_Remarks P_
____ NICKNAME

PATIENT_HISTORY
PH_ID
____ PH_Date PH_Symptom_Desc PH_Diagnosis
PH_DIAG PH_Status P_ID

• Remove ‘P_Nickname’ column from the PATIENT table


ALTER TABLE PATIENT
DROP COLUMN P_NICKNAME;

• Rename the column ‘PH_Diagnosis’ of PATIENT_HISTORY as ‘PH_Diag’


ALTER TABLE PATIENT_HISTORY
RENAME COLUMN PH_DIAGNOSIS TO PH_DIAG;

38
Removing Tables from a database
• Remove tables from a database
DROP TABLE PATIENT;
DROP TABLE PATIENT_HISTORY;

39

You might also like