cs2141-as4-f18-v4
CSCI 2141: ASSIGNMENT-4
Given: November 20th, 2018
Due: December 4th, 2018 (midnight)
Objective:
With this assignment, you will put together all the different parts of the database design and implementation life cycle and create a 3-Tier Database System. So, let’s startJ
Questions:
-
1) Design a relational database schema for a database application of your choice. (45% – break down is
below)
a) State and describe your requirements, i.e. business rules for the application you choose. You
may explore a similar existing system to come up with a list of requirements for your database
and its front-end application. You may also use one of the databases you designed in the
previous assignments as your starting point. (5%)
- b) Design and draw your Entity – Relationship Diagrams using MySql Workbench / Microsoft Word / yEd / or any other drawing tool of your choice. (5%)
-
c) Design and declare your relational data model using SQL (10% – break down is below)
(1) Minimum 3 relations (tables) (2%)
(2) Minimum 3 attributes per table (2%)
(3) Minimum 10 records per table (6%) - d) Normalize your database design to the level of 3NF: Use either a top-down or a bottom-up approach (10%)
-
e) The logical model of your database application should include: (15% – breakdown is below)
(1) Minimum one INSERT, one DELETE, one UPDATE query (3%)
(2) Minimum three SELECT queries (3%)
(3) Minimum one JOIN, one GROW BY, one VIEW query (3%) (4) Minimum one Trigger (3%)
(5) Minimum one Stored Procedure (3%)
- 2) Implement / Develop the physical model of your database application using MySql Workbench. Your MySQL code is the third tier (backend data tier) of your 3-Tier architecture. (25%)
- 3) The business logic forms the application tier, which is the second tier of your 3-Tier architecture. The application tier takes the information from the presentation tier and queries the data tier (backend). (20%)
- 4) The first tier of your 3-Tier architecture is the presentation tier which enables the client/user to access the database. This user interface could be a form to fill in, or a field to choose etc. depending
on your application and design. (10%)
Total: 100%
**Both for the first tier and the second tier, you can use any programming / scripting language of your choice to develop the user interface which forms the client access.
1
An Example for a 3-Tier System:
Imagine that you are designing a 3-Tier database system for a company selling different gift items. In this case, the first tier (presentation tier) could be a user interface where the user chooses the category of gift items such as books or CDs, gift wrapped or not etc. Then in the second tier (application tier), your application could let the user do any SQL operation only on the tables related to books if the user chooses books, or only CDs if the user chooses CDs. As for the third tier (data tier), you have your tables, attributes, records, triggers etc. all implemented on the MySQL database on Bluenose.
Submitting your assignment:
Please submit the following on Brightspace on the due date of the assignment before midnight:
- Your report for question-1.
- Your MySql Workbench code for question-2.
- Your application code / script for question-3.
- Your user interface code / script for question-4.
Please do not forget to write your Name, B-Number and your e-mail address on the first page of the assignment report / code you will submit.
If you plan to use one (or more) of your budget days for this assignment, please read the “Assignment Late Policy” section on the Class Syllabus document and e-mail our TA (Saurabh.Dey@Dal.Ca) accordingly.
If you are submitting late, you will still submit using Brightspace. However, please note that no assignment submissions will be accepted after December 9th, 2018 midnight!
Finally, please remember that if you have used all your budget days then “Late Policy” will apply.
If you have any questions, please see me, preferably earlier than the day before the assignment is dueJ
2