Database Design and ERD Creation
Subject: We will discuss the logical design, data definition and relationship between the tables of database.
Logical Design
A logical design is a conceptual & abstract design. In Logical database, you have deal with only defining the types of information that you need. Basically it is process to define logical relation between entities and attributes.
In our database, we have three table; Art, Artist and Art location and all tables have 4-4 field.
Art table
It contains five fields; Size, Genre, Value, Date Created, ART_ID (a primary key).
Artist Table
It contains five fields: ARTIST_ID (primary key), ART_ID (Foreign key), Name, Address, Telephone, and Amount of Art.
Art Location Table
It contains size fields; ID (PK), Section, Location, Start Date, End Date, and ART_ID (FK).
Data Types for Table
Data types, Key selection the most important things to do in database designing. In our, There are three tables. Data types, key definition of each table are given below:
Art Table
|
Field
|
Data Type
|
Key
|
Constraint
|
|
ART_ID
|
INT
|
PK
|
Auto Increment, Not Null
|
|
Size
|
INT
|
|
Not Null
|
|
Genre
|
VARCHAR(40)
|
|
Not Null
|
|
Value
|
VARCHAR(40)
|
|
Not Null
|
|
Date Created
|
Date/Time
|
|
Not Null
|
Artist Table
|
Field
|
Data Type
|
Key
|
Constraint
|
|
ARTIST_ID
|
INT
|
PK
|
Auto Increment ,Not null
|
|
ART_ID
|
INT
|
FK
|
Not null
|
|
Name
|
VARCHAR(50)
|
|
Not null
|
|
Address
|
VARCHAR(250)
|
|
Null
|
|
Telephone
|
VARCHAR(12)
|
|
Not null
|
|
Amount of Art
|
INT
|
|
Not null
|
Art Location Table
|
Field
|
Data Type
|
Key
|
Constraint
|
|
ID
|
INT
|
PK
|
Auto Increment, Not null
|
|
Section
|
VARCHAR(50)
|
|
not null
|
|
Location
|
VARCHAR(100)
|
|
not null
|
|
Start Date
|
Date/Time
|
|
not null
|
|
End Date
|
Date/Time
|
|
not null
|
|
ART_ID
|
INT
|
FK
|
not null
|
ERD Diagram
The following assignment is based on the database environment designed in the Week Two Individual Assignment.
Your database project must meet the following assessment requirements:
- Design a database using professional principles and standards.
- Provide a logical design of the database. As a part of the design, normalize the database to the 3NF
- Use Microsoft Visio or another tool to create this diagram to normalize the ERD to third normal form (3NF).