ICT701 Relational Database Systems Paper Editing Services

ICT701 Relational Database Systems Paper Editing Services

Part A: Database Design

The Proposed Solution

Jo Bloggs Auto Traders is a private sector organisation that sells new or used motor vehicles. With Emerging market, the company is looking forward to database migration. The database solution has to be efficient and reliable, so I have designed a database solution that fulfils all these initial requirements.

The database design that I have developed consists of ten entities. To eliminate the chances of update, delete and insert anomalies, I have normalized the database up to third normal form. I had to read the given case scenario multiple times to ensure the completeness of the work.

While designing the database I did not know how the details of old and new cars can be saved separately. I searched for a solution over the internet and got to know about the concept of aggregation. I have added the “old car” and “new car” entities as weak entities in my design. I also learned that “many to many” relationship are not suitable for a relational database design. So I invoked associative entities in my design as a solution to this problem. I noticed that a car may have many extra items attached to it. In such a case it would become a multi-valued attribute. So, in order to resolve this problem I considered creating a separate table for the extra items.

Entity Relationship Model

The following diagram is the proposed entity relationship model for Jo Bloggs Auto Traders.

Figure 1 Entity Relationship Diagram

Relational Data Model (RDM)

Following table represents the relational data model for Jo Bloggs Auto Traders.

Part B: Normalization Process

Un-Normalized Database

COMPACT_DISK(title number, album name, distributor id, distributor name,((track number, track name, track duration, ((artist id, artist name, date of birth, age, instrument_type, instrument description )) )) )

First Normal Form

A database can be in first normal form, only when all the attributes in it have atomic values. Considering this problem I have broken down the relation into following parts.

COMPACT_DISK(title number, album name, distributor id, distributor name)

Track(track number, track name, track duration)

Artist(artist id, artist name, date of birth, age, instrument_type, instrument description)

Second Normal Form

In the “Compact_Disk” relation, the candidate key is the combination of “Title_Number” and “Distributor id”. The value of the “Distributer name” attribute depend only on the “Distributor id”. So, to remove this partial dependency I have broken down the “Compact_Disk” relation into two parts as follows.

COMPACT_DISK(title number, album name)

Distributor(distributor id, distributor name)

Track(track number, track name, track duration)

Artist(artist id, artist name, date of birth, age, instrument_type, instrument description)

Third Normal Form

A database should not have any transitive functional dependencies for being in third normal form. In the “Artist” relation, the “instrument type” is dependent on the “artist id” attribute. And the “instrument description” attribute is dependent on the “instrument type”. I have added a dedicated primary key “Instrument id” for the new instruments table. Our database will be in third normal form with following structure.

COMPACT_DISK(title number, album name)

Distributor(distributor id, distributor name)

Track(track number, track name, track duration)

Artist(artist id, artist name, date of birth, age)

Instrument(instrument id, instrument_type, instrument description)

Part C: MySQL Queries

Query 1

CREATE TABLE employee (

    EMP_NUM CHAR(3),

    EMP_LNAME VARCHAR(15),

    EMP_FNAME VARCHAR(15),

    EMP_INITIAL CHAR(1),

    EMP_HIREDATE DATE,

    JOB_CODE CHAR(3) 

);

Query 2

1.INSERT INTO `autotraders`.`employee` (`EMP_Num`, `EMP_LNAME`, `EMP_FNAME`, `EMP_INITAL`, `EMP_HIREDATE`, `JOB_CODE`) VALUES ('107', 'Alonzo', 'Maria', 'D', '1993-10-10', '500'), ('108', 'Washington', 'Ralph', 'B', '1991-08-22', '501');

2.INSERT INTO `employee`(`EMP_Num`, `EMP_LNAME`, `EMP_FNAME`, `EMP_INITAL`, `EMP_HIREDATE`, `JOB_CODE`) VALUES ([101],[News],[john],[G],[2000-11-08],[502]);

Query 3

SELECT * from employee where  JOB_CODE ='502';

Query 4

Commit `employee`;

Query 5

1.UPDATE employee SET JOB_CODE = '501' WHERE EMP_Num = 107

2.SELECT * FROM `employee`

3.UPDATE employee SET JOB_CODE = '500' WHERE EMP_Num = 107 ;

Query 6

1.CREATE TABLE EMP_2 As Select EMP_Num, EMP_LNAME, EMP_FNAME,    EMP_INITAL, EMP_HIREDATE, JOB_CODE from emplyee;

2.ALTER table Emp_2 ADD EMP_PCT Decimal(4,2);

3.ALTER TABLE emp_2 ADD  PROJ_NUM CHAR(3);

Query 7

UPDATE emp_2 SET EMP_PCT = 4.85 WHERE EMP_Num=103;

UPDATE emp_2;

SET EMP_PCT = 5.00

WHERE EMP_Num=101 ;

and so on…..

Query 8

UPDATE emp_2 SET PROJ_NUM = 18 WHERE JOB_CODE=500;

Query 9

UPDATE emp_2 SET PROJ_NUM = 25 WHERE JOB_CODE>=502;

Query 10

UPDATE emp_2 SET PROJ_NUM = 14 WHERE EMP_HIREDATE < '1994-01-01' And JOB_CODE >=501;

Query 11

(a)

1.CREATE TABLE TEMP_2 SELECT * FROM employee;

2.ALTER Table TEMP_1 ADD EMP_PCT Decimal(4,2);

3.ALTER TABLE emp_2 ADD  PROJ_NUM CHAR(3);

(b)

INSERT INTO `temp_1`(`EMP_Num`, `EMP_LNAME`, `EMP_FNAME`, `EMP_INITAL`, `EMP_HIREDATE`, `JOB_CODE`, `EMP_PCT`, `PROJ_NUM`) VALUES ([101],[News],[john],[G],[2000-11-08],[502],[0],[0]);

Query 12

DELETE FROM temp_1;

Query 13

SELECT * FROM `employee` WHERE EMP_LNAME LIKE'smith%';

Query 14

SELECT avg(`EMP_PCT`) FROM emp_2;

Query 15

SELECT * FROM `emp_2` order by `EMP_PCT` asc;

Query 16

SELECT DISTINCT PROJ_NUM FROM emp_2;