Design and Implementation of a Database for Warranty and Insurance Management

The provided content is an assignment that outlines a database design for a system that tracks products, warranties, insurance, and locations. The detailed ER diagram shows the relationships between the entities, including a one-to-one relationship between product ID and warranty ID, and a one-to-many relationship between location and product. The business rules dictate that each product has a unique ID, can have zero or more warranty information, and can have zero or one insurance information. The physical design adopts naming conventions such as all attribute names in upper case and table names in capital. The schema includes tables for goods, products, warranties, insurance, locations, and their corresponding columns with data types and constraints.

Northern Virginia Community College
PHYSICAL AND LOGICAL DESIGN
A Home Inventory Database
0 | P a g e
OCTOBER 22, 2016
Prabhat Chaudhary
ITD256 E70W

Contents
LOGICAL DESIGN..............................................................................................................................2
PROPOSED DESIGN.......................................................................................................................2
ENTITIES AND ATTRIBUTES.......................................................................................................2
DESIGN............................................................................................................................................3
DETAILED ERD..............................................................................................................................5
RELATIONSHIPS............................................................................................................................6
BUSINESS RULES...........................................................................................................................6
PHYSICAL DESIGN............................................................................................................................6
NAMING CONVENTIONS..............................................................................................................6
SCHEMA..........................................................................................................................................7
1 | P a g e

LOGICAL DESIGN
PROPOSED DESIGN
The ‘home inventory database’ logical design will contain different entities to store the
information for each of the following things:
1. Products or goods
2. Insurance information
3. Warranty information
4. Location information.
Since home inventory is something which keeps on growing with time and there arises a
need to buy new things, so we will have tables to store the list as well as purchases of
what has been purchased their cost, location information etc.
Also each entity will have some varied and additional attributes including the basic
attributes that were defined the previous phase. This has been done in order to meet the
hidden requirements that may arise in future. Apart from this, it will help in managing the
data more precisely. The data will atomic and each row will have a unique information.
So accordingly the keys will also be designed in the logical phase. All the entities will be
associated according to the previous requirements yet adding the new fields to them. The
plan below has entire logical design for the home inventory database’.
ENTITIES AND ATTRIBUTES
The entities and attributes that will be present in the database to meet all the requirements as
per the proposed design are as follows:
ENTITY ATTRIBUTES
GOODS
This entity will store general information about
the goods
PROD_ID (PRIMARY KEY)
PROD_NAME
PURCHASE_DATE
LOCATION_ID
COST
PRODUCT_INSURANCE INSURANCE_ID (PRIMARY KEY)
INSURANCE_STATUS
2 | P a g e

This entity will store the information about the
product insurance(if any) PROD_ID
INSURANCE_DETAILS
INSURANCE_COST
LOCATION
This entity will store the information about the
different locations from where the product has
been or can be bought
LOCATION_ID(PRIMARY KEY)
LNAME
WEB_ADDRESS
PHONE
GOODS_WARRANTY
This table will store the information about the
warranty of the product
WARRANTY_ID (PRIMARY KEY)
PROD_ID
WARRANTY_STATUS
START_DATE
END_DATE
DETAILS
DESIGN
All primary keys and foreign keys have been created and connected logically so as to connect
all the tables. Same structure will be followed when creating a physical design as well. After
applying normalization, the design is as follows:
ENTITY ATTRIBUTES
GOODS PROD_ID (PRIMARY KEY) ,
(FOREIGN KEY
GOODS(PROD_ID))
COST
PURCHASE_DATE
LOCATION_ID (FOREIGN
KEY
LOCATION(LOCATION_ID))
PROD_NAME
3 | P a g e

ENTITY ATTRIBUTES
INSURANCE PROD_ID(FOREIGN KEY
GOODS(PROD_ID))
INSURANCE_STATUS
INSURANCE_ID (PRIMAR
KEY)
INSURANCE_DETAILS
INSURANCE_COST
ENTITY ATTRIBUTES
LOCATION LOCATION_ID (PRIMARY
KEY)
LNAME
WEB_ADDRESS
PHONE
ENTITY ATTRIBUTES
GOODS_WARRANTY PROD_ID (FOREIGN KEY
GOODS(PROD_ID))
WARRANTY_ID (PRIMARY
KEY)
WARRANTY_STATUS
START_DATE
END_DATE
DETAILS
4 | P a g e

DETAILED ERD
The detailed ER diagram will all the above attributes, keys and relationships has been
designed so as to prepare a visual design of how the database will actually look. The diagram
below shows the same:
RELATIONSHIPS
The relationships developed in the above ER diagram as per the requirements and the
proposed design are as follows:
5 | P a g e

1. Each product will have a purchase information if it has been purchased, else it will be
null.
2. ProductID in products has a 1:1 relationship with productID in warranty because a
limit of one warranty will be purchased for a product.
3. Each warranty record must be associated with one and only one product.
4. Information of each product which ‘has’ insurance, will be stored.
5. The relationship between location and product will be 1:M because a location might
be associated with multiple products, but each product must come from one and only
one location.
BUSINESS RULES
The business rules as per the ER diagram design are as follows:
1. Each product will have a unique id called PROD_ID
2. Each product will have zero or more warranty information associated to it.
3. Each product will have zero or one insurance information associated to it.
4. Each location will have unique it called LOCATION_ID
5. Each product will have a name.
6. Each warranty will have a unique id called WARRANTY_ID
7. Each insurance record will have a unique id called INSURANCE_ID
8. Each product is associated to only one location whereas each location will be
associated with one or more products.
PHYSICAL DESIGN
NAMING CONVENTIONS
The naming conventions that will be adopted while implementation of the physical design
are:
1. Each attribute name will be in upper case.
2. Each primary will have ‘_ID’ suffixed to id.
3. Each table name will be in capital.
4. Each value entered into the table will be in lower case.
5. The cost will have decimal values.
6 | P a g e

SCHEMA
The schema designed to implement the above ER diagram contains the following things:
1. Table names
2. Column names
3. Column description
4. Data types
5. Constraints including keys and values
TABLE NAME COLUMN
NAME
COLUMN
DESCRIPTIO
N
DATA
TYPE
CONSTRAIN
TS
GOODS PROD_ID Unique id for
each product
INTEGER PRIMARY
KEY
PURCHASE_DATE Date of
purchase of
product
DATE FORMAT
(‘DD-MM-
YYYY’) ,
NOT NULL
LOCATION_ID Id of the
location
INTEGER FOREIGN
KEY
LOCATION
(LOCATION_I
D) , NOT
NULL
COST Cost of the
product
NUMBER
(10,2)
NOT NULL
PROD_NAME Name of
product
VARCHA
R(50)
NOT NULL
PRODUCT_
INSURANCE
INSURANCE_ID Unique id for
each
VARCHA
R(10)
PRIMARY
KEY
INSURANCE_
STATUS
Status of the
insurance
CHAR(3) Values ‘yes’ or
‘no’, NOT
7 | P a g e

whether present
or not
NULL
PROD_ID Id of the
product
INTEGER FOREIGN
KEY GOODS
(PROD_ID),
not null
INSURANCE_
DETAILS
Details related
to the insurance
VARCHA
R(100)
INSURANCE_
COST
Cost of the
insurance
NUMBER
(10,2)
LOCATION LOCATION_ID Unique id for
each
INTEGER PRIMARY
KEY
LNAME Name of the
location.
VARCHA
R(30)
NOT NULL
WEB_ADDRESS Web url of the
location
VARCHA
R(30)
NOT NULL
PHONE Phone number
of the location
NUMBER
(10)
Unique
GOODS_
WARRANTY
WARRANTY_ID Unique id for
each
VARCHA
R(10)
PRIMARY
KEY
PROD_ID Id of product INTEGER FOREIGN
KEY GOODS
(PROD_ID),
not null
WARRANTY_
STATUS
Status of the
warranty
whether present
or not.
CHAR(3) Values ‘yes’ or
‘no’, NOT
NULL
START_DATE Start date of
warranty
DATE FORMAT
(‘DD-MM-
YYYY’)
END_DATE End date of the DATE FORMAT
8 | P a g e