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.

Deliverables:

1. Scope Of Work document from step 1

2. Reports document(s) from step 2

3. Brainstorm list and functional dependencies from steps 3 &

4.

4. Database model from step 5 and modified in step 7 (only 1 file

submitted, please)

5. Inserts.txt from step 6

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.