17 Aug In this DB project you need to demonstrate your database design and SQL skills through a selected business scenario.
company/business (scenario)- Bank of America
project description
Assignment Type: Group project of four students with the same major.
Posted on: Sep 22
Project submission due date: Nov 30
Grading: You will earn 100 points if you return your assignment complete, in time, and present eloquently. You will lose 15 points for each late day (24 hour). Each members of a group will be graded individually, meaning that members of a group can have different grades.
Submission: Only one member of each group is required to upload project files through D2L portal. Use template (340_Project_Template, under same chapter on D2L) for your report. Do not change fonts, color, and settings (margins). Do not change text/paragraph/page format options of Word File. Use only one page for each step. You just need to add/change text/add image in this template. During the presentation you need to demonstrate your project via Project Report.
Deliverables: 1) Project Report in pdf format, name as Group_<GroupNumber>340_Fall_2021.pdf. Web address for .php files should be added to report. Website should be active until last day of final week.
Objective: In this DB project you need to demonstrate your database design and SQL skills through a selected business scenario.
DESCRIPTION
In this project you need to create your imaginary scenario for a given database problems below. A project is around a business scenario, similar to the mini-cases developed in the class, Company ER and database.
You plan business scenario roughly first. Then, you may need to refine it. For instance, if ER design invalidates database design rules (1NF, 2NF, and 3NF) you need to fix these problems accordingly. Each group will present their case in a 10-minute in-class or online class presentation. The instructor will choose the presenter at the time of presentation. After the group’s presentation, class members and the instructor may ask questions about your scenario/project.
Project Details
Below, you will find the steps you should follow in DB design and SQL statements.
DB Design
Step 1) Create an imaginary business around your business theme. Your business theme will be given later. Your scenario should satisfy following conditions:
a. Include at least one one-to-one binary relationships.
b. Include at least one one-to-many binary relationships.
c. Include at least one many-to-many binary relationships.
d. Include at least one intersection data (on many-to-many relationships).
e. Include at least one one-to-one unary relationships.
Note-1: You can create more relationships if required.
2) Explain the story behind the scenario, and all your assumptions, which are required to support relationships given above.
3) Show show entities and their attributes. Each entity should have at least three attributes.
4) Show ER diagram with correct notation (cardinality/modality). You can use any drawing software, MS Visio, www.lucidchart.com, www.draw.io , etc. In those online tool you can use “Entity Diagram” section. Make sure that you ER diagram fits one pages and seems tidy and compact.
5) Show your referential integrity rules for your scenario. (FK->PK)
6) Convert the ER diagram to tables. Show your tables with primary keys.
7) Discuss database normalization rules on your tables. Do not explain what the rules are. Instead validate if each of tables satisfies 1NF, 2NF, and 3NF. If not, normalize your tables.
SQL
In this part you need to demonstrate SQL operation on your project portal (phpMyAdmin). Add related SQL statement and output as screenshot into your project file. Make sure that each table at least 10 rows and each table has appropriate PK and FK assignments.
8) List each of table, such as STUDENT( CWID (PK), Name, LastName, Major (FK) ). No screenshot required, SQL statements only.
9) Give data insertion SQL statements involving at least three tables. (SQL only)
10) Give data deletion SQL statements involving at least three tables. (SQL only)
11) Give data update SQL statements involving at least three tables. (SQL only)
12) Give two SELECT statements with WHERE statement. Also show it online, a link for .php file required. Show screenshot of .php file.
13) Give two SELECT statements with GROUP BY statement. Show the resulting table (screenshot).
14) Give a SELECT statements with HAVING statement. Show the resulting table (screenshot).
15) Using two related tables (meaning logically connected with primary-key and foreign-key pairs), run a join statement to show matching rows. For instance, assume that Table A and Table B has 4 and 5 attributes respectively. Also, assume that Table A’s primary key is seen as foreign key in Table B. Use join operations to show matching rows whose primary key and foreign key is same. Give SQL statement and show the resulting table (screenshot).
16) Give a Left Join statements. Also show it online, a link for .php file required. Show screenshot of .php file.
17) Give a ORDER BY statement with join statement on a foreign key. Also show it online, a link for .php file required. (Either ascending or descending order is acceptable). Give SQL Statement. Show screenshot of .php file.
18) Give a SQL statement in which a DATE data type is subject of where statement (such as, select … from … where birthday > DATE). Also show it online, a link for .php file required. Show screenshot of .php file.
19) Give a CREATE VIEW statement. Show the resulting table (screenshot).
Grade points:
Student Involvement: 20 pts,
Report Formatting: 10 pts
Q1: 10 pts
Q2 – Q4: 8 pts each, 24 pts
Q5 – Q10: 3 pts each, 18 pts
Q11 – Q19: 2 pts each, 18 pts
Total: 100 pts
