M.
Chinyuku Database Systems 2022
MySQL Practical 2- SQL Scripts
Create database
1. Create Database Syntax
CREATE DATABASE database_name;
Example : create a database named EXERCISE1
Activity 1
CREATE DATABASE EXERCISE1;
Click the SQL tab
Type CREATE DATABASE EXERCISE1;
Click Go button
Page 1|6
M. Chinyuku Database Systems 2022
Create Table
2. Create Table syntax
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
Activity 2
Example: create a table called PET with the following structure. PET (NAME, OWNER, SPECIES, SEX,
BIRTH, DEATH)
Table 1
Pet
Name Owner Specie Sex Birth Death
s
Foxy John Dog M 15 October 2013 1 January 2020
Kwaipa John Cat F 21 October 2015 31 October 2020
Delphi Mary Cat F 14 December 2019 5 September 2020
Click the exercise1 database from phpMyAdmin
Click the SQL tab
Type script to create table PET
Page 2|6
M. Chinyuku Database Systems 2022
Click the Go button
Populate table PET table
Exercise 1
The following business rules describe the TEACH database:
A lecturer can teach several courses
A lecturer should teach at least one course in a semester
Each course is taught by one lecturer in a semester
Not all courses can be taught in a semester
Entity Relationship Model (ERD) -refer to lecture 6
ERD is a conceptual model that shows all the data requirements for a organization.
Use SQL script to create database TEACH using the Relational Schemas below: refer to lecture 8
LECTURER(LNum, LName)
COURSE (CNum, CName,LNum)
NOTE: LNum is a foreign key in COURSE table
Solution
Page 3|6
M. Chinyuku Database Systems 2022
Use your own data to populate table LECTURER
Use your own data to populate table COURSE
Foreign key Constraints
PARENT TABLE
A B
a1 b1
a2 b2
CHILD TABLE
X A
x1 a1
x2 a1
x3 a2
Restrict
• RESTRICT option prevents the removal (i.e. using delete) or modification (i..e using an update) of
rows from the parent table.
• means that any attempt to delete and/or update the parent will fail throwing an error.
NO Action
• There will not be any change in the referencing rows when the referenced ones are deleted or
updated.
• Same as restrict
Cascade
• Delete: the referencing rows will be deleted automatically along with the referenced ones.
• IF WE DELETE row 1(a1) FROM parent it will delete rows 1 and 2 entries from child.
• Problem need to keep history
• CASCADE if the parent primary key is changed, the child value will also change to reflect that.
Page 4|6
M. Chinyuku Database Systems 2022
• IF WE Update in row 1(a1) FROM parent it will update rows 1 and 2 entries from child.
Set Null
• On Delete/Update the value of referencing record will be set to NULL automatically along with
the referenced ones.
Activity 3
Develop a database with the following business requirements
“A departments have several lecturers. A department should have at least one
lecturer. A department is defined by a unique name. Each lecturer should belong
to only one department and can teach at least one course. A lecturer is defined by
a unique lecturer number and name. Each course has a unique ID and name. A
lecturer teaches a course during specific times in various venues.”
Solution
ERD
Page 5|6
M. Chinyuku Database Systems 2022
Relational Schemas
Department(DeptName)
Lecturer (LecturerNo, LecturerName,DeptName)
Course(CourseID, CourseName, )
Class(ClassID, CourseID, LecturerNo, Venue, Times)
Use your own data to populate the tables you created above
Page 6|6
