Some Business Theme Examples:

$$ Banking data management

$$ Insurance data management

$$ Manufacture Inventory Management

$$ Airline data management

$$ Bookstore data management

$$ College data management

$$ Library data management

$$ Hospital data management

$$ Student database management

You may pick one of the above scenarios or define your own imaginary business scenario.

It$$s okay if your select a business scenario that$$s similar to previous assignments or the mini cases demonstrated in the class slides or illustrated in the book, but please modify it so it has some variance.

Project Details

Below, you will find the steps you should follow in DB design and SQL statements.

DB Design

$1)(10$pts$)$ Describe an imaginary business around your business theme. Your scenario should satisfy the 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$).$

$($Optional$)$ You can create more relationships if needed.

Explain the story behind the scenario, and all your assumptions, which are required to support relationships given above.

$2)(10$pts$)$ Show entities and their attributes. Each entity should have at least three attributes, show primary keys, as well as foreign keys if exist. Draw ER diagram with correct notation $($cardinality$/$modality$).$ You can use any drawing software, MS Visio, diagrams.net $($formerly www$.$draw.io$),$ etc. In those tools you can use "Entity Diagram" section. Insert your ER diagram screenshot in your project report. If it does not fit in one page, you may upload a separate jpg file for your ER Diagram.

$3)(10$pts$)$ Define some referential integrity rules $($for example: delete rules$)/$constraints for your scenarios.

$4)(10$pts$)$ Convert the ER diagram to tables. Show your tables with primary keys, as well as foreign keys if exist.

$5)(10$pts$)$ Discuss database normalization rules on your tables. Do not explain what the rules are. Instead check and validate if each of tables satisfies $1$NF$,2$NF$,$ and $3$NF$.$ If not, normalize your tables. Explain how you achieved normalization.

SQL

In this part you need to demonstrate SQL operations in MySQL Workbench or other database design tool.

Write SQL statements to:

$6)(5$pts$)$ Write SQL statements to create your database and tables resulted from step $1$ through $5.$

$7)(5$pts$)$ Demonstrate data insertion into at least three tables or more.

$8)(5$pts$)$ Demonstrate data deletion with at least two tables.

$9)(5$pts$)$ Demonstrate data update with at least one table.

$10)(5$pts$)$ Demonstrate a SELECT statement with WHERE statement.

$11)(5$pts$)$ Demonstrate a SELECT statement with GROUP BY statement.

$12)(5$pts$)$ Demonstrate a SELECT statement with ORDER BY statement.

$13)(5$pts$)$ Demonstrate a SELECT statement with HAVING statement.

$14)(5$pts$)$ Using two related tables $($meaning logically connected with primary$-$key and foreign$-$key pairs$),$ join these two tables 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$.$ Join two tables to show matching rows whose primary key and foreign key is the same.

$15)(5$pts$)$ Demonstrate a SQL statement in which a DATE data type is subject of where statement

$16)(5$pts$)$ Demonstrate CREATE VIEW statement

Note:

For each step from $6)$ through $16),$ do the following:

a$)$ Write your SQL Statements in MySQL Workbench or other database design tool

b$)$ Run the statement.

c$)$ Take screenshot of the statements as well as its running output if any

d$)$ Add the screenshots into your project report.

e$)$ Write simple explanation about what the SQL command does.

Save your SQL commands to a file $$sqlCode$\_$YourFullName.sql$,$ upload this sql file along with your Final Project Report document. $(5$ bonus points for uploading this sql file$),$