cs2141-as4-f18-v4
1
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 start J
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.
2
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:
1. Your report for question-1.
2. Your MySql Workbench code for question-2.
3. Your application code / script for question-3.
4. 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 due J