Database Practical Project 2016

This is a practical assignment for a Databases course at the College of Computing Technology in 2016. The project requires students to build a database based on their previous design, create six tables with constraints, insert data, define four SQL query reports, create a DataAnalytics table, write commands for backup and recovery, and create a trigger. Students must also write a reflection on their experience. The submission includes a zipped folder with a word document, SQL statements, and a database dump. The assignment is weighted at 20% and is due on December 23, 2016.

College of Computing Technology Databases, 2016
Databases
Module Title: Database
Assignment Type: Practical
Project Title: Build the database you designed in CA4
Project Date: Friday 16th December 2016
Assignment Compiler: David O’Neill, Conor O’Reilly
Weighting: 20%
Due Date: Friday 23th December 2016
Method of Submission: Moodle
Feedback Method: On Marking Sheet using Rubric below
Module Learning Outcome: Design and construct a database (PLO 3,6)
Recognize and deal with RDBMS constraint violations(PLO 8)
Manipulate the data in a RDMBS (PLO 5,7)
Transactional processing (PLO 2)
Practical Exercise
Build the database you designed in CA4 and write a reflection on your experience. The steps
involved in this practical exercise are as follows:
1. Create the database
2. Create the six tables with the primary keys and constraints
3. Insert data based on the subject matter area associated with your database.
a. All tables should have at least 5 rows of data.
b. At least one of the base tables should have 50 rows of data.
c. At least one of the link tables should have 20 rows of data which record the
association between two of the base tables.
4. Define four SQL query reports (see definition in the report requirements section)
5. Insert 5 rows of data into the DataAnalytics table, associate the individuals in the
DataAnalytics table with one of your table based on type of access such as: Read Access,
Write Access, Update Access, Delete Access.
6. Create a select statement that provides a report on each Data Analyst and the privileges
they have to tables in the database.

7. Write the commands to create a backup of two of the tables in your database, then the
commands to drop existing tables then recover the tables from your backup.
8. Create a trigger on any table that will insert a new row into a table called history (you
must create this table). You must use the new values from the row and store them in
the history table. Set the trigger to fire AFTER INSERT.
9. Write approx. 200 word reflection as regards the most important concept/approach you
learnt during this practical, what was the biggest mistake you made and what you would
do differently if you had to do this exercise again.
Submission Requirements
Submit a zipped folder containing the following on Moodle
1) A word document containing:
a) Your name and student number
b) Your approx 200 word reflection – deliverable 9
c) The statements used for deliverable 5,6,7,8 in a word document called commands.
Include a screen shot from deliverable 6.
d) The four report SQL statements created and for each provide a screen shot of the
output – deliverable 4 – save the commands in a document called reports.
2) If excel spreadsheets were used to create the insert statements (which it should have been,
if you want to save yourself a lot of typing) add it to the folder. If you use multiple INSERT
statements save them in a file called my_inserts.sql - deliverable 3
3) A single file with all the tables and relationships (e.g. a dump of the entire database)
2

Report Requirements
You are to create minimum of four SQL queries that report on your database tables. The
reports should be of the following types:
Report #1
Provide an SQL script that queries one of your base tables the query should perform the
following
 A projection e.g. Restrict the number of column shown
 A selection e.g. and use the WHERE keyword to restrict the number of rows returned
 Use ORDER BY
 Change the column headings using the AS keyword
Report #2
Provide an SQL script that queries one of the link tables and one of the base tables using a
JOIN the query should perform the following
 A projection that includes columns from both tables e.g. Sales.Customer_ID,
Customer.CustomerName
 A JOIN keyword e.g. to specify how the tables are joined
 A selection e.g. and use the WHERE keyword to restrict the number of rows returned
based on a date column
 Change the column headings using the AS keyword
Report #3
Provide an SQL script that queries one of your base tables the query should perform the
following
 A projection e.g. Restrict the number of column shown
 Perform a calculation on one of the columns e.g, multiply the column value by 100
 Use AS keyword to give the column a name
 A selection e.g. and use the WHERE keyword to restrict the number of rows returned
Report #4
Provide an SQL script that queries one of your base tables the query should perform the
following
 A projection e.g. Restrict the number of column shown
 Apply the following text function to LEFT, SUBSTRING, UPPER to three of your
VARCHAR data type columns (http://www.w3resource.com/mysql/mysql-tutorials.php)
 A selection e.g. and use the WHERE keyword to restrict the number of rows returned
 Use an AND, or OR keyword
3

Submission Notes
1. Save all material in a folder called
studentName_studentNumber_CCTDatabase2016_Practical
e.g. DavidONeill_2015000_CCTDatabase2016_Practical
2. Zip the folder and submit the zip file of the folder via Moodle before or on the due data
e.g. DavidONeill_2015000_CCTDatabase2016_Practical.zip
Marking Scheme Summary
Description Weighting
Database with data built with scripts 30
The four report Select statements 40
The DataAnalytics link table and select statement 10
The backup, drop and source statements 5
The trigger statement 5
The reflection 10
Total 100
4