MONASH
INFORMATION TECHNOLOGY
Creating & Populating the Database – Data Definition Language
User requirements
Relational model theories
Oracle syntax
Draw Conceptual conceptual ERD
ERD
Draw logical level Model
Logical level Model
Write database schema
Database schema
Business rules
ERD notations
Normalisation
2
2
SQL general syntax
▪ A single statement is ended with SEMICOLON.
▪ Predefined KEYWORDs represent clauses (components) of a statement.
▪ Keywords are NOT case sensitive. ▪ Examples:
3
3
SQL Statements
▪ Data Definition Language (DDL) – Creating database structure
• CREATE TABLE, ALTER TABLE, DROP TABLE ▪ Data Manipulation Language (DML)
– Adding and Manipulating database contents (rows) • INSERT, UPDATE, DELETE
– Retrieving data from database • SELECT
▪ Data Control Language (DCL) – Set permissions on objects
• GRANT
4
4
Q1. There are a number of business rule represented by the above model. Choose true statement(s) according to the diagram.
A. B.
C. D. E.
5
5
CREATE A TABLE (DDL)
6
7
7
Q2. What relational model component(s) is/are defined in this create table statement?
CREATE TABLE STUDENT (
stu_nbr NUMBER(6) NOT NULL,
stud_lname VARCHAR2(50) NOT NULL,
stud_fname VARCHAR2(50) NOT NULL,
stu_dob DATE NOT NULL, CONSTRAINT STUDENT_PK PRIMARY KEY (stu_nbr)
);
A. Relation, Attribute, Domain
B. Primary Key
C. Foreign Key
D. Referential Integrity constraint
E. All of the options in a-d are correct.
F. Some of the options in a-d are correct.
Common ORACLE data types
▪ Text: CHAR(size), VARCHAR2(size)
– e.g., CHAR(10), VARCHAR2(10)
– CHAR(10) → ‘apple’ = ‘apple ‘
– VARCHAR2(10) → ‘apple’ != ‘apple ‘
▪ Numbers: NUMBER(precision, scale)
–Weight NUMBER(7) or NUMBER(7,0) → Weight = 7456124 –Weight NUMBER(9,2) → Weight = 7456123.89
–Weight NUMBER(8,1) → Weight = 7456123.9
▪ Data/Time: DATE, TIMESTAMP
– DATE can store a date and time (time to seconds), stored as Julian date
– TIMESTAMP can store a date and a time (up to fractions of a second)
– TIMESTAMP WITH TIME ZONE
8
8
Column VS Table Level Constraints
CREATE TABLE STUDENT (
stu_nbr NUMBER(6) NOT NULL,
stud_lname VARCHAR2(50) NOT NULL,
stud_fname VARCHAR2(50) NOT NULL,
stu_dob DATE NOT NULL,
CONSTRAINT STUDENT_PK PRIMARY KEY (stu_nbr)
column constraints
);
table constraint
9
9
10 10
Q3. What would be the order of the CREATE TABLE statements in the schema script to successfully create a database based on the below diagram? (assuming that we will define the FK as part of the create table statement)
A. UNIT, ENROLMENT, STUDENT
B. ENROLMENT, STUDENT, UNIT
C. STUDENT, UNIT, ENROLMENT
D. UNIT, STUDENT, ENROLMENT
E. More than one option is correct
11 11
Q4. How many foreign key/s (FK) will be in the database when the three tables are created?
A. B. C. D.
CREATE TABLE student (
stu_nbr
stu_lname
stu_fname
stu_dob
CONSTRAINT pk_student PRIMARY KEY (stu_nbr)
);
CREATE TABLE unit (
unit_code CHAR(8) NOT NULL,
unit_name VARCHAR(50) CONSTRAINT uq_unit_name UNIQUE NOT NULL , CONSTRAINT pk_unit PRIMARY KEY (unit_code)
);
NUMBER(8) VARCHAR(50) VARCHAR(50) DATE
NOT NULL, NOT NULL, NOT NULL, NOT NULL,
12 12
CREATE
TABLE enrolment (
stu_nbr
unit_code
enrol_year
enrol_semester CHAR(2)
enrol_mark NUMBER(3) ,
enrol_grade CHAR(2),
CONSTRAINT pk_enrolment PRIMARY KEY (stu_nbr, unit_code, enrol_year, enrol_semester), CONSTRAINT fk_enrolment_student FOREIGN KEY (stu_nbr) REFERENCES student (stu_nbr), CONSTRAINT fk_enrolment_unit FOREIGN KEY (unit_code) REFERENCES unit (unit_code)
);
NUMBER(8) NOT NULL, CHAR(8) NOT NULL, NUMBER(4) NOT NULL,
NOT NULL,
13 13
CREATE TABLE enrolment (
stu_nbr unit_code enrol_year enrol_semester mark
);
ALTER TABLE enrolment ADD
( CONSTRAINT fk_enrolment_student FOREIGN KEY (stu_nbr) REFERENCES student ( stu_nbr),
CONSTRAINT fk_enrolment_unit FOREIGN KEY (unit_code) REFERENCES unit (unit_code));
NUMBER(8) NOT NULL, CHAR(8) NOT NULL, NUMBER(4) NOT NULL, CHAR(2) NOT NULL, NUMBER(3),
CHAR(2),
CONSTRAINT pk_enrolment PRIMARY KEY (stu_nbr, unit_code, enrol_year, enrol_semester)
grade
14 14
Referential Integrity
▪ To ensure referential integrity, SQL defines three possible actions for FKs in relations when a deletion of a primary key occurs:
– RESTRICT (Oracle No Action basically equivalent)
• Deletion of tuples is NOT ALLOWED for those tuples in the table referred by the FK (the table containing PK) if there is corresponding tuple in the table containing the FK.
– CASCADE
• A deletion of a tuple in the table referred by the FK (the table containing PK) will result in the deletion of the corresponding tuples in the table containing the FK.
– NULLIFY
• A deletion of a tuple in the table referred by the FK (the table containing PK) will result in the update of the corresponding tuples in the table containing the FK to NULL.
15 15
Referential Constraints SQL Data Modeller
16 16
What Referential Integrity Constraint to implement?
▪ Use the model to decide on what referential integrity constraint to implement.
– Mandatory vs Optional participation.
▪ The constraints must be decided at the design phase.
17 17
18 18
Q5. Assume that the table ENROLMENT contains enrolment details for students in FIT9132 and FIT9001. The referential integrity constraint is CASCADE. What would happen to tuples in ENROLMENT with the unit_code=’FIT9132′ when we delete the FIT9132 record from UNIT?
A. They will be deleted.
B. The value of unit_code will be updated to NULL.
C. The deletion is not possible, the DBMS will prevent the deletion.
D. None of the above.
19 19
Q6. What would happen to the student record with stu_nbr=’1234′ in the STUDENT table when we delete all tuples with stu_nbr=’1234′ in the ENROLMENT table? (Assume referential integrity is CASCADE constraints )
A. Student record with stu_nbr=’1234′ in the STUDENT table will be deleted.
B. Nothing will happen to the STUDENT table.
C. The stu_nbr=’1234′ in the STUDENT table will be updated to NULL.
D. Deletion will not be permitted by the DBMS.
20 20
Q7. What referential integrity constraint could be implemented according to the above model for the FKs in the PROJECT table without violating the business rules depicted in the model?
A. NULLIFY
B. CASCADE
C. RESTRICT
D. b and c are correct.
E. a, b and c are correct.
▪ Used to change a tables structure.
▪ For example:
– Adding column(s).
– Removing column(s).
– Adding constraint(s).
– Removing constraint(s)
21 21
Referential Integrity Definition – Example
ALTER TABLE enrolment
DROP CONSTRAINT fk_enrolment_student;
ALTER TABLE enrolment
DROP CONSTRAINT fk_enrolment_unit;
ALTER TABLE enrolment ADD
( CONSTRAINT fk_enrolment_student FOREIGN KEY (stu_nbr) REFERENCES student ( stu_nbr) ON DELETE CASCADE,
CONSTRAINT fk_enrolment_unit FOREIGN KEY (unit_code) REFERENCES unit (unit_code) ON DELETE CASCADE
);
22 22
DELETING A TABLE
▪ Use the DROP statement. ▪ Examples:
– –
23 23
ADDING TUPLES/ROWS TO A TABLE (DML)
24
INSERT
• •
25 25
26 26
Q8. Assume the tables have been created with primary and foreign key constraints and there is no data currently in the tables. In what order should we populate the table?
A. UNIT- > ENROLMENT -> STUDENT
B. STUDENT -> ENROLMENT -> UNIT
C. STUDENT -> UNIT -> ENROLMENT
D. More than one option is correct.
COMMIT and ROLLBACK
27 27
•
–
•
–
–
•
28 28
Q9. Two new students and their enrolment details need to be added, James Bond wants to enrol in FIT9132 and FIT9001, Bruce Lee only wants to enrol in FIT9132. The sequence for sno is called sno_seq. What problems, if any, exist with this script:
A.
B. C. D. E.
F.
29 29
PUTTING THIS TO WORK
30
31 31
32 32
33 33