DATABASE DESIGN AND IMPLEMENTATION Database Design

Running head: DATABASE DESIGN AND IMPLEMENTATION
Database Design and Implementation
Name of the Student
Name of the University
Author’s note:

1DATABASE DESIGN AND IMPLEMENTATION
Table of Contents
Project Description:.....................................................................................................................................2
Library Database Design Requirements:.................................................................................................2
Entity Relationship Diagram:...................................................................................................................4
Business Rules:........................................................................................................................................4
Normalization Process:............................................................................................................................5
Functional Dependencies:.......................................................................................................................5
Query, Report and Forms:.......................................................................................................................5
Implementing Library Database Design:......................................................................................................6
Create Tables:.........................................................................................................................................6
Insert Data:..............................................................................................................................................8
SQL Commands:........................................................................................................................................10
Bibliography:.............................................................................................................................................12

2DATABASE DESIGN AND IMPLEMENTATION
Project Description:
Library Database Design Requirements:
The database can be considered as the set of data associated to a specific purpose or subject. The
database is either installed in a computer or in a cloud. The relational database system is the most popular
database type. The relational database is based on implementing relation among the entities stored in the
database. Prior to design the database, the designer must design the whole database. Without a good design it
is not possible to implement a fully functioning and scalable database. The database design include the entity
relationship diagram, relationships, attributes, constraints and many other things.
The fundamental steps for designing a complete database are as following.
i. Determining the database implementation purpose
ii. Identifying the entities required to support the purpose
iii. To recognize the attributes that can be the basic structure of the entities
iv. To understand the constraints that will be applied to the attributes
v. Recognizing the relationships among the tables
vi. Making the database entities in 3rd Normal form
vii. Adding data to the entities so that it can be meaningful
Determining the Database Purpose: The database is prepared for storing the data and activities done
in the library. The database will be storing the details of the members, books and rents mainly. However, the
other process associated with the library will need to be recorded. Taken as an example, a member can search
books of a specific writer. Therefore, storing the information of each author and which books they have
written is important. The library also impose fines when the books are not returned on time. Therefore the
database will also store the fine related information. It is essential to complete the planning part before start
implementing the database in Oracle. It is because, planning can seem to be time consuming but if not planned
properly a complex database can take ten times more time to be completed without planning.
Determining the Tables: This is considered to be one of the trickiest parts in the whole database
design process. This part is extremely tricky as the outcome of the database does not provide any assumptions
regarding the structure of it. The structure has to be done from scratch without having no idea of what can be
the outcome, up to a certain point. This process needs to be done by an extremely skilled and experienced
person. It is essential that database structure can prevent storing same data twice. However, for meeting the
purpose few data will be used various time but this repetitive data must be restricted only one in the whole
table. All the columns of the table must be related to the purpose of the database. Taken as an example, if the

3DATABASE DESIGN AND IMPLEMENTATION
database just wants to store member data then it is not required to store parent data. However, in case of
hospital system it is essential to have a relative data so that in case emergency he/she can be contacted. Each
data in the table must in proper data type. Just because Varchar2 can store almost all kind of data, a designer
can use only Varchar2. Taken as an example, in case of money decimal or similar data type must be used.
The indexing has been introduced into the database for optimizing the performance of the database.
This achieved through reducing the number of disks required to access for executing a query. The index can be
considered as a type of data structure which can be used for locating and accessing the data within a database
very efficiently. The indexing methods are four types such as ordered indices, primary indices, clustering
indices and secondary indices. The primary indices can be further be categorized into dense and sparse indices.
If the name column of the member is indexed, then the database can easily search the name as the table will
reduced to the name column only. This way the efficient identification of data is achieved through the indexing
method.
Determining the Fields: The attributes serves as the basic static structure of the entities. The attributes
represent different characteristics of real world representation of attributes. The attributes should have single
value. The reason will be demonstrated in the normalization step.
Constraints: Constraints can be of many types like unique, primary, referential, check, not null and
many more. The declaration of constraints are extremely important for defining the dynamic nature of the
database entities. Each of the mentioned constraints define a specific characteristic of the attribute. It is not
necessary that every attribute must have a constraint but to make an entity fully operational and correct it is
essential to add constraints at required attributes.
Relationships: The relationships determines how the database would respond to user request. The
relationships can be divided into two parts, such as cardinality and optionality. The cardinality means one or
many row of an entity can be related to one or many rows in another table. Taken as an example, one member
can rent many books. The optionality means a row may be related to row to another table. Zero means can or
cannot be related and One means that row have to be associated to at least one row of another table. Taken
as an example, a book may not borrowed by any member. In this case, the book will have zero optionality with
the rent entity. However, a rented book has to be from the book entity in this case rent will have One
optionality with book.
Normalization: Normalization is the process of preventing any data anomalies from the database. The
normalization has up to 5 normal form. However, for most of the databases, 3rd normal form is taken into
consideration. The firs normal form states that the tables must have atomic values (no multivalued attributes