Relational Database Systems

Running head: RELATIONAL DATABASE SYSTEMS
Relational Database Systems
Name of the Student
Name of the University
Author’s Note

1
RELATIONAL DATABASE SYSTEMS
Table of Contents
PART A......................................................................................................................................3
Approaches for modelling......................................................................................................3
Entity Relationship Diagram..................................................................................................3
Supplementary design requirements......................................................................................3
Assumptions...........................................................................................................................4
PART B......................................................................................................................................4
Creating tables from ERD......................................................................................................4
SQL Code to enter data from spreadsheet..............................................................................4
SQL Code for listing Postcode of 4556.................................................................................6
SQL code for changing postcode to 6501 for the customer ID AA0621...............................7
SQL code for adding the attribute CUST_LEVEL and CUST_DISCOUNT to the customer
table........................................................................................................................................7
SQL command sequences for updating CUST_LEVEL and CUST_Discount.....................7
SQL Command for displaying first and last names of the customers who have not ordered
books......................................................................................................................................8
SQL command sequence for listing all customers who have ordered books.........................9
SQL command sequence for increasing the retails of all books by $20 (Computer) or if
cost > $40...............................................................................................................................9
SQL command for listing ISBN and Book Title for the expensive book in the technical
book database.......................................................................................................................10
SQL command that identifies the profit of each book.........................................................10

2
RELATIONAL DATABASE SYSTEMS
SQl command for listing the customer name starting with Smith.......................................11
SQL commands for creating view for the customer table without username and password
..............................................................................................................................................11
Trigger for automatically updating the cust_level and cust_discount when a customer
purchases a book..................................................................................................................11
SQL command for deleting data from all the tables............................................................12
Bibliography.............................................................................................................................12

3
RELATIONAL DATABASE SYSTEMS
PART A
Approaches for modelling
The entity relationship modelling is used for designing the database and identification
of the flow of information. The elements are needed for the development of the database are
identified and their relationship is mapped visually for the development of the database.
Entity Relationship Diagram
Supplementary design requirements
For the development of the tables in the ER diagram the entities are identified and the
attributes used for defining the elements are used. 5 tables are created namely customer,
supplier, publisher, books and order. The tables are normalized for eliminating the duplicate