Build the Database You Designed


Added on  2019-09-19

4 Pages955 Words313 Views
College of Computing TechnologyDatabases, 2016DatabasesModule Title:DatabaseAssignment Type:PracticalProject Title:Build the database you designed in CA4Project Date: Friday 16th December 2016Assignment Compiler: David O’Neill, Conor O’ReillyWeighting: 20%Due Date:Friday 23th December 2016Method of Submission: MoodleFeedback Method:On Marking Sheet using Rubric belowModule 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 database2.Create the six tables with the primary keys and constraints3.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.
Build the Database You Designed_1

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 youlearnt during this practical, what was the biggest mistake you made and what you woulddo differently if you had to do this exercise again.Submission RequirementsSubmit a zipped folder containing the following on Moodle1)A word document containing:a)Your name and student numberb)Your approx 200 word reflection – deliverable 9c)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 33)A single file with all the tables and relationships (e.g. a dump of the entire database)2
Build the Database You Designed_2

End of preview

Want to access all the pages? Upload your documents or become a member.

Related Documents