Information System: Data Modeling

This assignment focuses on creating a logical data model for a car dealership database. Students are tasked with designing the database schema, including tables for manufacturers, dealers, customers, cars, and sales. The assignment emphasizes applying normalization rules to ensure data integrity and efficiency. The solution includes SQL code for creating tables and a class diagram illustrating the relationships between entities. A second part of the assignment involves normalizing a dataset related to CD information, applying the principles of 3NF. The provided solution includes class diagrams for both parts of the assignment, demonstrating the relationships between entities and attributes, and adhering to the principles of database normalization.

ASSIGNMENT: Create a Class Diagram
Subject: Information System
Topic: logical data model
THE PURPOSE OF THIS ASSIGNMENT IS TO
CREATE A LOGICAL DATA MODEL AND APPLY
RULES OF NORMALIZATION TO THE MODEL
Page 1

Assignment: Create a logical data Model for the dealership keeps information about each car
manufacturer with whom it deals so that employees can get in touch with manufacturers easily.
The dealership also keeps information about the models of cars that staff the dealership carries
from each manufacturer. They keep such information as list price, the price the dealership paid to
obtain the model, and the model name and series (e.g., Honda Civic LX). They also keep
information about all sales that they have made. (For instance, they will record the buyer’s name,
the car he or she bought, and the amount he or she paid for the car.) So that staff can contact the
buyers in the future, contact information is also kept (e.g., address, phone number).
According to the assignment Jim Smith keep track of information of manufacturer and car
information according to the class diagram on cars model can be deals by different dealer and
also manufacturer can deals with many dealers. According to the requirement the cardinality of
the relations may change.
According to the assignment we have generate the database query to show the relation between
manufacturer, dealer, seller and buyer.
MANUFACTURER
CREATE TABLE MANUFACTURER
(
M_ID NUMBER,
ADDRESS VARCHAR2 (30),
CONTACT_PERSON CHAR (20),
PHONE NUMBER,
FAX VARCHAR2 (50),
EMAIL VARCHAR2 (50),
, # Here Dealer ID and manufacturer ID are the primary key.
DEALER_ID NUMBER REFERENCES DEALER (DEALERSHIP_ID),
CONSTRAINT MANUFACTURER_PK PRIMARY KEY ( MANUFACTURER_ID )
);
===================================================================
Here I have design class diagram for all the available relation.
Page 2

Class Diagram
Manufacturer
MID ( Primary Key)
Address
Phone
Fax
Email
Contact Person
Cars
VIN (Primary Key)
Make
Model
Series
Yeae
List Price
Customers
Cust ID (Primary Key)
First Name
Last Name
Address
Phone
Email
Sales
Sales Order ( Primary
Key)
Cust ID
DATE
VIN
Price
Dealer
DealerShipID
Dealer Name
Authorised
Contact No
Page 3

DEALER
CREATE TABLE DEALER
(
DEALERSHIP_ID NUMBER,
DEALER_NAME VARCHAR2 (50) ,
CONTACT_NO NUMBER,
AUTHORISED
#dealership Id is a primary key.
CONSTRAINT DEALER_PK PRIMARY KEY (DEALERSHIP_ID)
);
===================================================================
SALES
CREATE TABLE SALES
(
SALE_ORDER NUMBER
CUST_ID NUMBER, ,
SALE_DATE NUMBER,
VIN NUMBER,
PRICE NUMBER,
# Sale order is a primary key.
CONSTRAINT SALES_PK PRIMARY KEY (SALE_ORDER)
);
====================================================================
CUSTOMERS
CREATE TABLE BUYER
(
CUST_ID NUMBER,
FIRST_NAME VARCHAR2(50)
LAST_NAME VARCHAR2(50)
ADDRESS_LINE1 VARCHAR2 (50)
PHONE NUMBER,
EMAIL VARCHAR2(50)
# Customer Id Is A Primary KEY.
CONSTRAINT BUYER_PK PRIMARY KEY (CUST_ID)
);
=============================================================
Page 4

CARS
CREATE TABLE CAR
(
VIN NUMBER NOT NULL,
MAKE VARCHAR2 (50) NOT NULL,
MODEL_NAME VARCHAR2 (20) NOT NULL,
SERIES VARCHAR2 (50) NOT NULL,
YEAR DATE NOT NULL,
PRICE NUMBER NOT NULL,
# VIN Number is primary key.
DEALER_ID NUMBER REFERENCES DEALER (DEALERSHIP_ID),
CONSTRAINT CAR_PK PRIMARY KEY ( VIN )
);
======================================================
Assigmnnt: Apply the rules of normalization to the model to check the model for processing
efficiency
Normalization is the process for organizing the data into the table format in such way where the
results of the database are always unambiguous. it also effect the duplicity if available in the
database. see the example.
 1 item of a SALES serves 1 to many CUSTOMERS
 1 item of a CUSTOMER places 1 to many ORDERS
 1 item of an ORDER lists 1 to many PRODUCTS
 1 item of a CAR stores 0 to many PRODUCTS
Example for show the normalization
Page 5

According to the assignment here I have design the relationship in the form of normalization also
establish the communication between Manufacturer, Cars, Customer, and Sales.
Note: PK-->Primary key, FK-->Foreign Key, VIN--> Vehicle identification Number
Page 6

D. You have been given a file that contains fields relating to CD information. Using
the steps of normalization, create a logical data model that represents this file in third
normal form. The fields include the following:
• Musical group name
• Musicians in group
• Date group was formed Group’s agent
• CD title 1
• CD title 2
• CD title 3
• CD 1 length
• CD 2 length
• CD 3 length
The assumptions are as follows:
• Musicians in group contain a list of the members of the people in the musical group.
• Musical groups can have more than one CD, so both group name and CD title are needed
to uniquely identify a particular CD.
According to the Assignments here i have prepared the class diagram for show the relation
between Musician and his group. we can see in the class diagram in the musical group, group_ID
and Musician_ID shows the primary key. Similarly
 In musician Block Group_ID and Musician_ID are the primary key.
 In Groups agent Agent_ID is the primary key.
 In CD block Group_ID and CD_ID are the primary key.
Page 7

Class Diagram
Musicians
*Group_ID ( Primary Key)
*Musician_ID( Primary Key)
Musician_Name
Phone
Group's Agents
Agent_ID( Primary Key)
Agent_Name
Phone
Musician_Name
CD title and length
CD Length
CD 1
CD 2
CD 3
CD
*Group_ID
*CD_ID
CD Title 1
CD Title 2
CD Title 3
Musical Group
*Group_ID
Group Name
*Agent ID
*Musician_ID
Date Formed
Address
Page 8

According to the assignment we have to create a Data model for the file is using third
normal form:
The basics fundamental of the 3rd normal form:
 In this data model, repeated attributes are eliminated
 All the attributes ii the entity are dependent on primary key attributes
 The non-key attributes that are dependent on other non-key attributes
are removed.
Page 9

Reference
Guru99. 1NF, 2NF, 3NF & BCNF with Examples: Retrieved From
https://www.guru99.com/database-normalization.html
Class Diagram: Retrieved From
https://en.wikipedia.org/wiki/Class_diagram
Sujit Maharjan(2017). Restaurant Billing System. Vaasan Ammattikorkeakoulu University of Applied
Sciences, Degree Program in Information Technology(pp. 1-61). Retrieved from
https://www.theseus.fi/bitstream/handle/10024/140862/Maharjan_Sujit.pdf?sequence=1
Page
10