Develop a database for one of the following: Library Police Department Fire Depa

Question

Develop a database for one of the following:

Library

Police Department

Fire Department

Humane Society

Utilities Department (Electric/Water/Sewage)

Post Office



Step 1: Pick the service from above. Decide on scope of work. What will be included and what will not. Pick at least 8 entities to represent in your scope. Document this in a word processor and save as ScopeOfWork.



Step 2: Mock up 3 different, unrelated reports to be used by the business you chose in either a word processor or spreadsheet program. Make the data fields on these reports realistic. Two of the reports must pull data from 2 different entities, and 1 report should pull data from 3 entities. If you are not sure if you have done this correctly, revisit this step after step 4.

Insert some fake data. If you think of any new entities in this step, add then to the document from step 1. Refer to Heather Sweeney from chapter 4 as an example. Save the reports with the name Reports. If you have more than 1 document, name then Reports1, Reports2, etc.



Step 3: List all of the fields you need to store in order to generate your reports. Then brainstorm for more fields until you have a total of at least 40 fields, which may or may not be related to the reports. This step helps you to think of other data that this company might want to track, beyond the reports given. Save this list in a document called Brainstorm.



Step 4: Create functional dependencies for all of the fields listed in the previous step. Make sure you have at least 8 entities. If not, go back to step 3 and think of some more fields which would occur under another entity. Add this at the end of the Brainstorm document, after the list of fields. Remember, every field must either be a determinant or a dependent. If it is neither, then it probably doesn't belong in the database. Return to step 2 to verify your reports use fields from multiple entities.



Step 5: Create a normalized, physical model ERD (relational model) for the entities in the database. Use MySQL Workbench for this step. Set up the relationships as you see fit. Use a new database (schema) name for this model using your last name. Save the model file with your last name.



Step 6: Build the database on your computer using the SQL generated in the previous step into your schema (verify this). Add at least 5 rows of fake data into all of the tables using SQL Insert statements. Save the insert statements into a text file called Inserts.txt.



Step 7: Generate 3 views to pull the data required in your 3 reports. Name the views Report1View, Report2View, and Report3View. Use the database to test the syntax and validity of the views. Then, reopen the model file from step 5 and add the views into the model. Resave the model file.



Step 8: Use MySQL Administrator to backup the database to a file. Name the backup file with your last name and the default date.



Put this backup and all electronic documentation in a folder and submit the zipped folder.



Deliverables:

ScopeOfWork document from step 1
Reports document(s) from step 2
Brainstorm list and functional dependencies from steps 3 & 4.
Database model from step 5 and modified in step 7 (only 1 file submitted, please)
Inserts.txt from step 6
Database backup file.

Explanation / Answer

CREATE DATABASE database_name [ ON { [ PRIMARY ] [ [ ,...n ] [ , [ ,...n ] ] [ LOG ON { [ ,...n ] } ] } ] [ COLLATE collation_name ] [ WITH ] ] [;] To attach a database CREATE DATABASE database_name ON [ ,...n ] FOR { ATTACH [ WITH ] | ATTACH_REBUILD_LOG } [;] ::= { ( NAME = logical_file_name , FILENAME = { 'os_file_name' | 'filestream_path' } [ , SIZE = size [ KB | MB | GB | TB ] ] [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ] ) [ ,...n ] } ::= { FILEGROUP filegroup_name [ CONTAINS FILESTREAM ] [ DEFAULT ] [ ,...n ] } ::= { [ DB_CHAINING { ON | OFF } ] [ , TRUSTWORTHY { ON | OFF } ] } ::= { ENABLE_BROKER | NEW_BROKER | ERROR_BROKER_CONVERSATIONS } Create a database snapshot CREATE DATABASE database_snapshot_name ON ( NAME = logical_file_name, FILENAME = 'os_file_name' ) [ ,...n ] AS SNAPSHOT OF source_database_name [;]

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.