A precise table or diagram is essential. Deliver a perfect solution urgently. Strict warning: AI tools are prohibited.

BR$17$

"Advance SQL

Database Principles

Tools MYsql and Mysql Workbench

Write a database creation script, using the database below and answer the following questions

User $($UserID$,$ Email, Password, FirstName, LastName$)$

FamilyMember $($FamilyMemberID$,$ FirstName, LastName, Relation, UserID, Phone Number$)$

Add RewardPoints column to FamilyMember table $($initialize to zero$)$

Activity $($ActivityID$,$ Description, Type, Priority$)$

ActivityAssignment $($AssignmentID$,$ ActivityID, FamilyMemberID, DueDate, AssignedDate$)$

$=======================================================================$

To better explain the database here are the Relationships between each entities:

User has many FamilyMembers

FamilyMember belongs to User.

FamilyMember has many ActivityAssignments

Activity has many ActivityAssignments

ActivityAssignment belongs to FamilyMember and Activity

QUESTION $1$

a$.$ The database, and all its tables, are in $3$NF

b$.$ The SQL script can be run more than once

c$.$ All columns are using the most appropriate type

Program language$($JAVA$)$

$2.$ Using the database creation script from question one, create a JDBC program

that implements three unique workflows. A workflow is defined as a user selected

operation that results in a database call and some business logic being executed.

You must:

a$.$ Identify your three workflows

b$.$ Include a presentation layer that manages how the user

selects, and interacts with, your three workflows.

c$.$ Include a business logic layer that implements functionality

relevant to your three workflows. I do not want you to just request data

from the database and display it to the user. Add some interesting

functionality here.

d$.$ Include a DAL layer that:

i$.$ Manages the database connection

ii$.$ Calls stored procedures or functions, and returns the results to the

business layer in an efficient manner.

Ensure that your program considers storage and efficiency concerns

by caching static data where appropriate.

We have seen that going to the database, and more importantly to main

memory, is an expensive operation. Our code should only go to the

database when it needs to$.$ Why make multiple database calls, only to get

the same data every time? This is why our DAL should be smart enough

to cache data."