Info6001 Database Management 1 : Assessment Answers
Students are to create a database design specification (entity relationship diagram (ERD) and relational data model (RDM)) from the given business description below. Students are to present this in a report (as specified in the submission format) which also includes a short discussion of the approach for the solution. This assignment is to be carried out and reported individually.
The following defines the minimum information that should be reported:
1. A brief discussion of your solution, i.e. how you approached the modelling problem and any issues you may have encountered
2. The Entity Relationship Diagram (ERD) (produced using a drawing tool such as those found in MS Word or PowerPoint, or a chosen CASE tool) for the system. Your ERD must use the ER notation that was taught in ICT701. Any assumptions made in creating the ERD must be explicitly stated in your report.
3. The Relational Data Model (RDM) which corresponds to the ERD. That is, the set of tables in 3rd normal form that could be used to represent the whole of this data model. Your RDM should identify primary, alternate and foreign keys.
Resolve the following relation to 3rd Normal Form. It is a requirement to show both the 1NF and 2NF that you found as part of the process in moving to 3NF.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
Complete the following SQL tasks using MySQL. The marks available for each SQL task are indicated.
1) Write the SQL code that will create the table structure for a table named EMPLOYEE. The basic EMPLOYEE table structure is summarized in the table below.
4) Write the SQL code that will save the changes made to the EMPLOYEE table.
5) Write the SQL code to change the job code to 501 for the person whose employee number (EMP_NUM) is 107. After you have completed the task, examine the results, and then by using SQL reset the job code to its original value. All SQL code to complete this full task must be shown in your submission.
6) Write the SQL code to create a copy of EMPLOYEE, naming the copy EMP_2. Then write the SQL code that will add the attributes and associated constraintsEMP_PCT and PROJ_NUM to its structure. The new attribute characteristics are:
EMP_PCT NUMBER(4,2)
PROJ_NUM CHAR(3)
7) Write the SQL code to change the EMP_PCT value to 3.85 for the person whose employee number (EMP_NUM) is 103. Next, write the SQL command sequences to change the EMP_PCT values as shown in the table below. .
Answer:
Part A
- A brief discussion on the solution and the in modelling the problem and any issues encountered
The modelling process began with indentifying the entities and their attributes. An entity was either classified as regular or weak entity. An attribute on the other hand was either grouped as key attribute, simple attribute, composite attribute, single valued attributes, multi-valued attributes, stored attribute or a derived attribute The next process involved indentifying the relationships between the entities. This includes the degree of a relationship, the cardinality of a relationship and the relationship participation. The last process involved sketching the entities and their relationships. The final step was drawing the actual ERD using draw.io -an online tool for drawing UML diagrams.
- The Entity Relationship Diagram (ERD)
Assumptions made:
- A manufacturer can supply more than one vehicle
- A manufacturer can have one or more addresses
- Only one client may buy a particular vehicle but several salespeople may share the sale.
- Address and Extras are weak entities because they depends on other entity for its existence and doesn't have any key attribute of their own
Part B
Resolving relation to 3rd Normal Form
- INF
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)
- 2NF
COMPACT_DISK (title number, distributor id)
ALBUM (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 (instrument_type, instrument description)
- 3NF
COMPACT_DISK (title number, distributor id, artist_id)
ALBUM (title number, album name, artist_id)
DISTRIBUTOR (distributor id, distributor name)
TRACK (track number, track name, track duration, artist_id)
ARTIST (artist_id, artist name, date of birth, age, instrument_type)
INSTRUMENT (instrument_type, instrument description)
Part C
- CREATE TABLE `employee` ( `EMP_NUM` char(3) NOT NULL, `EMP_LNAME` varchar(15) NOT NULL,`EMP_FNAME` varchar(15) NOT NULL, `EMP_INITIAL` char(1) NOT NULL, `EMP_HIREDATE` date NOT NULL, `JOB_CODE` char(3) NOT NULL);
- insert into `employee` (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE)values(101, 'News', 'John','G', '08-Nov-00', '502');
insert into `employee` (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE)values(102, 'Senior', 'David','H', '12-Jul-89', '501');
insert into `employee` (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE)values(103, 'Arbough', 'June','E', '01-Dec-96', '500');
insert into `employee` (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE)values(104, 'Ramoras', 'Anne','K', '15-Nov-87', '501');
insert into `employee` (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE)values(105, 'Johnson', 'Alice','K', '01-Feb-93', '502');
insert into `employee` (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE)values(106, 'Smithfield', 'William','', '22-Jun-04', '500');
insert into `employee` (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE)values(107, 'Alonzo', 'Maria','D', '10-Oct-93', '500');
insert into `employee` (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE)values(108, 'Washington', 'Raph','B', '22-Aug-91', '501');
insert into `employee` (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE)values(109, 'Smith', 'Larry','W', '18-Jul-97', '501');
- select* from employee where job_code='502';
- COMMIT;
- updateemployee set job_code=501 where emp_num=107;
Rollback;
- CREATE TABLE 'EMP_2'('EMP_NUM' CHAR(3) NOT NULL,'EMP_LNAME' VARCHAR(15) NOT NULL,'EMP_FNAME' VARCHAR(15) NOT NULL,'EMP_INITIAL' CHAR(1),'EMP_HIREDATE' DATE NOT NULL,'JOB_CODE' CHAR(3) NOT NULL);
INSERT INTO EMP_2 SELECT * FROM EMPLOYEE;
ALTER TABLE EMP_2 ADD (EMP_PCT NUMBER (4,2)),ADD(PROJ_NUM CHAR(3));
- update emp_2 set emp_pct=3.85 where emp_num=103;
update emp_2 set emp_pct=5.00 where job_code=502;
update emp_2 set emp_pct=8.00 where emp_num=102;
update emp_2 set emp_pct=10.00 where emp_num=104;
update emp_2 set emp_pct=6.20 where emp_num=106;
update emp_2 set emp_pct=5.15 where emp_num=107;
update emp_2 set emp_pct=10.00 where emp_num=108;
update emp_2 set emp_pct=2.00 where emp_num=109;
- update emp_2 set PROJ_NUM=18 where job_code=500;
- update emp_2 set PROJ_NUM=25 where job_code=>502;
- update emp_2 set PROJ_NUM=14 where EMP_HIREDATE<='01-Jan-94' AND JOB_CODE >='501';
- Create table temp_1 as select emp_num, emp_pct from emp_2;
Insert into temp_1 select emp_num, emp_pct from emp_2;
- Droptable temp_1;
- select * from employee where EMP_LNAME like 'Smith%';
- Select avg(emp_pct) from emp_2;
- Select * from emp_2 order by emp_pct;
- Select distinct proj_num from emp_2;
References
Domanski P. (2000). A Practical Guide to Relational Database Design. UK: Diaxon Ltd
Richard E., Bagui S. (2011). Database Design Using Entity-Relationship Diagrams, Second Edition. USA: CRC Press
Yeslam A. (2017). How to draw an ERD from business rules. Retrieved 4th October 2017 from https://www.youtube.com/watch?v=YvJ4t9_2SWk
Buy Info6001 Database Management 1 : Assessment Answers Online
Talk to our expert to get the help with Info6001 Database Management 1 : Assessment Answers to complete your assessment on time and boost your grades now
The main aim/motive of the management assignment help services is to get connect with a greater number of students, and effectively help, and support them in getting completing their assignments the students also get find this a wonderful opportunity where they could effectively learn more about their topics, as the experts also have the best team members with them in which all the members effectively support each other to get complete their diploma assignments. They complete the assessments of the students in an appropriate manner and deliver them back to the students before the due date of the assignment so that the students could timely submit this, and can score higher marks. The experts of the assignment help services at urgenthomework.com are so much skilled, capable, talented, and experienced in their field of programming homework help writing assignments, so, for this, they can effectively write the best economics assignment help services.
