Week 5: Database Implementation

Database System
Dr.Eng. Yusuf Sulistyo Nugroho
Course Outline:
• Table Design
• Manual Implementation
• Alter Table
• Referential Integrity

2
Table Designing
• Based on the E-R Diagram, a database could be implemented.
• Several ways to implement database:
• Manually (using SQL ‘CREATE TABLE’)
• Semi-manual using GUI based client (MySQL Front, PgAccess,
phpPgAdmin, phpMyAdmin, etc.)
• Automatically using CASE Tools (DBDesigner)

3
Table Designing Steps
• Step 1:
• Create a new database / Use the existing database
• Create all main tables (have no FK)
• Step 2:
• Create all related tables (have relationships).
• Begin to create from table which has the least number of FK to the
most number of FK in order.
• Step 3:
• Repeat step 2 until all table have done.

4
Manual Implementation
• Set the Primary Key (PK):
> CREATE TABLE lecturer (
lecturer_id INTEGER PRIMARY KEY,
lecturer_name VARCHAR(45),
lecturer_address VARCHAR(255)
);
> CREATE TABLE room (
room_id VARCHAR(20) PRIMARY KEY,
location VARCHAR(255),
capacity INTEGER
);

5
Manual Implementation
• Set the Foreign Key (FK):
> CREATE TABLE student (
student_id INTEGER PRIMARY KEY,
student_name VARCHAR(45),
student_address VARCHAR(255),
lecturer_id INTEGER REFERENCES lecturer (lecturer_id)
);

6
Manual Implementation
• Create table that has many Foreign Keys (FKs):
> CREATE TABLE subject (
subject_id INTEGER PRIMARY KEY,
subject_name VARCHAR(45),
description VARCHAR(255),
lecturer_id INTEGER REFERENCES lecturer (lecturer_id),
room_id INTEGER REFERENCES room (room_id)
);

7
Manual Implementation
• Set the composite primary key (CPK):
> CREATE TABLE student_takes_subject (
student_id INTEGER REFERENCES student (student_id),
subject_id INTEGER REFERENCES subject (subject_id),
PRIMARY KEY (student_id, subject_id)
);

8
Alter Table
• Alter Table is used to modify an existing table in database.
• Syntax:
• ADD: to add a column in a table
• ALTER TABLE table_name ADD column_name datatype;
• DROP COLUMN: to delete a column in a table
• ALTER TABLE table_name DROP COLUMN column_name;
• ALTER COLUMN: to change the data type of a column in a table
• ALTER TABLE table_name ALTER COLUMN column_name datatype;
• RENAME COLUMN: to rename the column name in a table
• ALTER TABLE table_name RENAME COLUMN original_column_name TO
new_column_name;

9
Referential Integrity
• Database integrity refers to table relationships using their Foreign Key.
• To insert, record must be inserted to the main table, then insert it in
the second table.
• To delete, record must be removed in the second table, then remove it
in the main table.
• In default, most DBMS denies insert or delete instruction which break
the rules of database integrity.

10
Insert Table Data
• SQL instruction to insert data :
> INSERT INTO <tabel_name>
VALUES (<field _value>, …);
• Inserting data to specific columns.:
> INSERT INTO <table_name> (<field_name>, …)
VALUES (<field_value>, …);

• Warning: the sequence of field name must be similar to the sequence of field
values!

11
Procedure of Insert
The step of insert table follows the step of table creation.
• Step 1:
• Insert data in all main tables (which have no FK).
• Step 2:
• Insert data in all tables which relates to previous in the sequence of the number
of FK (start from the least number)
• Step 3:
• Repeat step 2 until they have done.

12
Delete Table Data
• To keep the database integrity, the step of deleting table data
is performed in the opposite way of the insertion step.
• In default, most DBMS deny delete instruction which break
the database integrity.
• In the other words, record in the main table will not be deleted if
there is a record in the second table connects to the main record.

13
Online SQL Tutorial
• You can also learn and try SQL instruction by yourself here:

https://www.w3schools.com/sql/default.asp

14
37
15