I'm stuck on how to do this and I'm running out of time, I've attached my previous weeks to help. 

 

The following assignment is based on the database environment designed in the Week Three Individual Assignment.

Your database project must meet the following requirements:

• Create a database using professional principles and standards.
• Use a relational database software application to develop a database implementing the logical design into a physical design.
• Use an Oracle® database to create the normalized physical database including tables, columns with data types, primary keys, constraints, etc.
• Create a minimum of ten rows of test data in each table.

Submit an .sql file with the scripts needed to create tables with keys and to insert test rows, etc., into the tablesT

 

James Doolan Week 3 Memo.doc

Memo
To:
Fro
m:
Clas
s:
Dat
e:
Re: Troy Tuckett
James Casey Doolan
DMB/380
02/06/17
Week 3 Normalization and ERD Creation Introduction
As we discussed the past few week, the Museum of the Galactic Empire is getting a
new database system. We will discuss the database design to help us with our art
management in the museum, the normalization of the database design and the ERD creating
of the normalized design of the database. Our museum has artwork displayed all over, we
need to know which piece is related to an artist, who made the art work and where is the art
work is being stored and this database will help us maintain this information.
Database Design
In a museum, a large as this one, you must be on top of everything, you don’t want to
lose track of a piece of artwork and you don’t want to a piece to be in the wrong area or under
the wrong artist, so we need to create a table that shoes this information about the artwork.
We must maintain information about the artist, the artwork, the section it belongs.
Entities and Attributes for Database Design
The entities for the display management of the art work in the museum are as follows:
1. Artist (Whom makes the art)
2. Art Work (Type of art)
3. Location (Where it’s located in the museum)
Artist:
This table will contain all the key information of the artist of the art work Name: (primary key) Address: Telephone Number: Amount of art:
Art Work
This table will contain all the key details about the art work: Value: Size: Date Created: Art ID (foreign key): Genre:
Location
This table will contain the details about the location of a piece of art work:
1 Section:
Location:
Location ID (foreign key)
Start date:
End Date: Normalization of the Database Design
First Normal Form (1NF)
In the first normal form of this database, we will check that none of the tables have
columns that has the same information. If we have a column that has the same information,
we will remove the columns to avoid confusion. In this case, we don’t have any duplicate
information in a column, so this will be how our 1NF will be:
Artist Table:
Name
(primary
key)
Art work table:
Value Location Table:
Section Address Size Telephone Date
Created Location Amount of
art Genre Location
ID Art ID
(foreign
key) Start
Date End
Date Second Normal Form (2NF):
The primary key is the name of the artist and the foreign keys are both the Art ID and
the Location ID. We will have the same primary key in the art work table but we will also add
the Art ID and the Location ID as primary keys for the Art Work table. In the location table, the
name of the artist will play the role of foreign key and the Location ID will play the role of the
primary key.
Artist Work Table:
Value
Siz
e Location Table:
Sectio
n Locatio
n Date
Create
d Star
t
Date Genr
e End
Dat
e Art ID
(primar
y key) Locatio
n ID
(primary
key Third Normal Form:
As shown in our 2NF, we might run into a situation where an artist has the same
name, so we must choose a primary key that is unique because it must be used in another Page 2 Artist
Name
(foreign
key) Art ID
(foreign
key) table. We will have the Artist ID as the primary key in our artist table and we will also have
the Artist ID as a foreign key in the artwork table. The 3NF of our database design is as
follows:
Artist Table:
Artist ID
Nam
Addres
Telephone
Amount
(primar
e
s
of Art
y key)
Art Work Table:
Value
Siz
e Location Table:
Sectio
n Locatio
n Date
Create
d Star
t
Date Genr
e End
Dat
e Art ID
(primar
y key) Locatio
n ID
(primary
key) Entity Relationship Diagram
Below is the ERM diagram for the normalized database of the Galactic Museum, this will
show the entities and their attributes relationships: Page 3 Artist
ID
(foreign
key)
Art ID
(foreign
key) Page 4 Works Cited
C, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Managment (11th edition
ed.). Murfreesboro, TN: Cengage.
Learnit_Admin. (2013, Nov 18). Know The Advantages And Disadvantages Of Microsoft Access.
Retrieved from Learn IT Anywhere: Know The Advantages And Disadvantages Of Microsoft
Access Page 5

 

Memo
To:
From
:
Class
:
Date:
Re: Troy Tuckett
James Casey Doolan
DMB/380
1/29/17
Individual Assignment for Week 2 Introduction
The museum of the Galactic Empire has new database system. All the entities and attributes
of this database system are discussed in this memo along with the selection of primary keys, foreign
keys and other constraints related to this database. In a museum where art work is displayed we identify
which piece is related to an artist, who owns the piece of artwork and the location of the art work. Entities and Attributes
The following are the details of entities and attributes along with appropriate data type for
each attribute and for its category. We chose an integer data type for amount and telephone number
attributes. We also chose a character data type for size and section of museum attributes because they
are represented by single character. While String data type is chosen for those text which is more than
one character. Date data type is used for date attributes like date created, start data and end date etc. in
database.
Entities
Art Work Attributes
Value
Size Genre 1 Artist Data Created
Art ID
Name Location Address
Telephone
Amount of Art
Section Small
Medium
Large
Paintings
Prints
Photos First Name
Last Name A Data Types
Integer
Character
Character
Character
String
String
String
Date
String
String
String
String
Integer
Integer
Character Location
Start Date
End Date
Location ID B
C
D
Side
Front Character
Character
Character
String
String
Date
Date
String The primary key for this database is the First Name of Artist because the art work is related
to an artist. The foreign keys are Art Id (which contain the first name of artist in addition of number of
art work of particular artist) and Location Id (which contain Art Id in addition of section A, B, C or D). Constraints
If we use a primary key other than this, then it is little bit difficult to relate other tables of art
work and location of art work in museum. Primary and foreign key will show the relationship of
artwork with artist and artwork and will include the location inside the museum. This will also show
which piece of art was made by artist, when that piece of art has been displayed in the museum and the
location has a relationship with the art work which is to be displayed at particular location of museum.
ERD Diagram
Below is the detailed ERD diagram for the database of museum which shows the entities and
their attributes and their relation. Page 2

Attachments:

• 1493793443-Week 2 Memo Template.doc
• 1493793446-James Doolan Week 3 Memo.doc