Prison Database System
1) Entities: Prison, Guard, Cell, Floor, Prisoner, Department, Manager, Employee.
A prison for male and female set up separately.
A prisoner is in 1 cell. One cell can host one or 2 prisoners.
A guard belongs to one department, and 1 department can have at least10 guards.
A cell is on one floor, and one floor can have at most of 20 cells.
A prison can have administration, health, and education department.
A department can one manager and a manager belongs to one department.
An employee belongs in one department, and a department should have 10 employees.
Create the E/R model of the database you are going to build. The more variety the better grade. (All kinds of relationships, associative entities, weak entities, multivalued attributes…).
1)Scan the diagram in a file or build the diagram in word or other text processing program.(ER file)
2)Create the database model in another file and keep it. (Database model file)
3)Normalize all the tables to the third Normal form and explain the process (Normalization file).
4)Create a database called LastName1_LastName2. (Last Name of the student )
5)Create the tables in the SQL Server program with SQL commands
6)Create the diagram of the database Model in SQL Server.
7)Insert an average of 10 records per table as long as the model allows it.
8)Try to retrieve data from the database.
9)Include the original info (excel file). In other words, how the data would look if no relational model is created. (Redundancy file)
10)Create non-clustered indexes in all columns of every non-bridge table. *(not confirmed yet)
11)When database is finished , back it up to a file called LastName1_LastName2.bak
12)Collect the E/R file, database model file, normalization file, redundancy fileand the bak file and zip it into one file called LastName1_LastName2.zip
13)Submit it in blackboard
Extra credit. (Up to 2 points of the final grade) Not mandatory:
Develop a Java Application that connects to the database you create and performs selects inserts updates and deletes to the tables through the Application.
Hint:Create a JQuery. Object and use the graphical objects (fields) . Make sure you have the JDBC driver well configured in your machine.
Do you need help with this assignment? Or a different one? We got you covered.