Data Modeling and SQL Language: A Comprehensive Report

Desklib provides past papers and solved assignments for students. This report covers database modeling and SQL.

DATA MODELING & SQL LANGUAGE
ASSIGNMENT 1
TABLE OF FIGURES
Figure 1: Hierarchical database model..................................................................................................4
Figure 2: Network Database Model.......................................................................................................5
Figure 3: Relational Database Model.....................................................................................................6
Figure 4: Top-down & Bottom up approach..........................................................................................7
Figure 5: Conceptual Data Model..........................................................................................................8
Figure 6: normalization of the auto seller entities.................................................................................9
Figure 7: AUTO SELLER ER DIAGRAM...................................................................................................10
Figure 8: AUTO SELLER RELATIONAL DATA MODEL.............................................................................10
Figure 9: create buyer table.................................................................................................................11
Figure 10: create body table................................................................................................................11
Figure 11: create car table...................................................................................................................11
Figure 12: create fuel table..................................................................................................................11
Figure 13: create purchased table.......................................................................................................11
Figure 14: create seller table...............................................................................................................12
Figure 15: create sold table.................................................................................................................12
Figure 16: insert statements of all tables............................................................................................12
Figure 17: SQL query for buyer table...................................................................................................13
Figure 18: SQL query for car table.......................................................................................................13
Figure 19: SQL query for seller table....................................................................................................13
Figure 20: SQL query for purchased count in February.......................................................................14
Figure 21: SQL query for sold count in February.................................................................................14
INTRODUCTION
Databases are a crucial part of our lives. In every segment, there is a database with the help of which
we search, rent, purchase, book and many other things on the internet. There are not many of us who
understands the database properly though it’s so important in today’s world. While attempting this
assignment I am now not among those anymore who don’t know how actually the databases work.

TASK 1
Difference between RDBMS & File Based System
RDBMS File-Based System
It is related to the database which is a
collection of data with the help of
programs & functions which can update,
create & interact.
It is related to the storing of data and
organizing it storing devices such as CD,
hard disk, floppy etc.
It stores data in rows and column table
structure that is used to connect related
data elements.
This type of system uses the indexing
system to find the data.
Advantages
The repetition of data i.e. data redundancy
is a problem that is easily solved with the
help of the Relational Database
Management System.
Advantages
The backup process in the file system is
really easy and fast as well.
Sharing of data is possible in case of
RDBMS because of its centralization
property.
Stored data in the computer-based systems
can be shared with multiple users at a time.
It has built-in operations for searching
which makes the overall search very easy.
To retrieve the data computer-based
systems provide functions which are easy
to use and efficient too.
The integrity of data is an issue which is
handled by the RDBMS by applying user-
defined constraints on the data.
In this system remote access of data is
possible.
Disadvantages
Due to the complex hardware requirement
the cost increases.
Disadvantages
The repetition of data is not handled well
in these systems i.e. data redundancy
It’s hard to manage the complexity which
occurs due to the presence of various
technologies.
Data integrity is not maintained in the file-
based systems as there are no constraints
to handle it.
Limitations
Cannot handle data such as image, audio
or video properly with the storage
available. Not adequate to work with any
other language except SQL.
Limitations
Data isolation and separation is difficult.
Changes to be made in a file structure is
too complex. There are incompatible file
formats so no easy access.

Advantages & Disadvantages of Database Models
HIERARCHICAL DATABASE MODEL
This database is one of the early database systems. It uses one too many multiple
relationships. It's based on the structure that one parent can have many children but children
will have only one parent.
ADVANTAGES
 It allows new information to be added or deleted easily.
 The topmost data is easy & fast to access as it is good with linear type storage of data.
 Data sharing is easy in this type of model.
 Security of the database is provided and is enforced by the database management
system.
 One to many relationships promotes efficiency as well as the integrity of data.
DISADVANTAGES
 Implementations are limited as there are no many to many relationships.
 If one wants to change the structure then changes will be made in all program
applications.
 Slow searching for data as many times it requires to store the data repetitively on the
low entities.
Figure 1: Hierarchical database model
NETWORK DATABASE MODEL
This model follows a convenient way of showing objects & their relationships. It is the same
as the hierarchical model but with a graph which allows it to have general connections among

the nodes. It can handle many to many relationships which the hierarchical wasn’t able to
handle. It means that a parent, as well as child, can have any number of children & parent
respectively.
ADVANTAGES
 The integrity of data is handled well as has various user constraints.
 Database design is easy and concept wise simple.
 Data access is easy as well as flexible.
 Real life object situations are handled well due to the many to many relationship-
based models.
 It has two languages DLL (Data Definition Language) & DML (Data Manipulation
Language) the database management system.
DISADVANTAGES
 Complexity is too much as maintenance of the records is done with pointers which
limit the efficiency.
 There is a big number of adjustments of the pointers to be made for the operations on
records like – deletion, insertion & updating.
 Changing the structure of the database is very tough due to the structural
independency absence.
Figure 2: Network Database Model
RELATIONAL DATABASE MODEL
In this type of database model, the data is stored & arranged in form of rows & columns
which follows Codd’s 12 rules. The functions which are there in the relational model are as
follows – accuracy, security, integrity & consistency.
ADVANTAGES
 Due to the independent structure format, it’s easy to make any changes in the database
without actually affecting the access.
 This model is simpler than that of hierarchical as well as the network model
conceptually.

 The implementation, usage, design & maintenance is really easy & simple.
DISADVANTAGES
 There are a lot of hardware requirements which in turn increases the overall cost of
the database model.
 As we know that the designing of the database in the relational model is very easy
which is good but the user doesn’t know how the data is being stored and how the
data access process is done. This ease can actually affect the design as these
inefficiencies will not be known to the user and in the end, he is only designing not
learning new things.
Figure 3: Relational Database Model
Strengths & weaknesses of the top-down & bottom-up approach to developing a
database
BOTTOM-UP APPROACH
 Strengths
o This approach improves the overall communication process in the team as
well as empowers the members of the team.
o Early phase benefits with the business & user getting aware of the product.
o Due to the automation starting at an early phase various manual processes can
be replaced.
o No need for custom adapters development.
 Weaknesses
o The might be a situation wherein the roll-out phase the whole structure of the
organization will have to be changed.
o The phase where all the planning is done is very effort & time consuming
when compared with the top-down approach.
o This approach is unsuitable for projects which are time sensitive.

TOP-DOWN APPROACH
 Strengths
o Implementation & decision making is quick when the time is limited.
o Easy to set the goals related to the project with those of the strategies related
to the organization growth.
o The showcase of the solution is the first implementation.
o Impact on the whole organization is low.
o Resources like maintenance & operation are not impacted initially like that of
the bottom-up approach.
 Weaknesses
o There is limited overall coverage provided by the solution in the first phase.
o In the first phase, a smaller number of user accounts are managed.
o There might be a need for custom adapter development at an early stage.
o Deployment costs are higher.
o Investment returns are delayed.
Figure 4: Top-down & Bottom-up approach
TASK 2
ENTITY LIST
 BUYER
 CAR
 SELLER
 SOLD
 PURCHASED
 FUEL
 BODY
CONCEPTUAL DATA MODEL

Figure 5: Conceptual Data Model
TASK 3
1. NORMALIZATION
It is the organizing of data present in the database to avoid insertion anomaly, redundancy of
data, deletion & update anomaly. It is a database design technique. It organizes tables in a
way that reduce modification anomalies. There are various types of normal forms which help
reduce anomalies of which some are listed below.
1NF OR FIRST NORMAL FORM
First Normal Form is any table which is able to define a relation. It removes all the duplicate
columns from the database.

We now have two entities linked by the car id key, two entities with seller id key and another
two with buyer id key. This means that each seller in the class is represented by an entry in
the sold table, so now there are no problems with varying numbers of cars sold. Same for the
buyer class, now no problems with counting the number of cars bought.
2NF OR SECOND NORMAL FORM
The table should be in 1NF and there should be only one column primary key. It removes the
partial dependency.
3NF OR THIRD NORMAL FORM
The table should be in 2NF. It removes transitive functional dependencies.
Figure 6: normalization of the auto seller entities
2. ENTITY RELATIONSHIP DIAGRAM

Figure 7: AUTO SELLER ER DIAGRAM
3. RELATIONAL DATA MODEL
Figure 8: AUTO SELLER RELATIONAL DATA MODEL
TASK 4

Figure 9: create buyer table
Figure 10: create body table
Figure 11: create car table
Figure 12: create fuel table
Figure 13: create purchased table

Figure 14: create seller table
Figure 15: create sold table
Figure 16: insert statements of all tables

Figure 17: SQL query for buyer table
Figure 18: SQL query for car table
Figure 19: SQL query for seller table