Data Modeling & SQL Language

Data Modelling & SQL Language

Student ID Number
(Do not include student
name as anonymous
marking is implemented)
Programme Title
Module Title
Module Code (listed on
Moodle and in LTAFP)
Module Convenor
Coursework Title
Academic Declaration:
Students are reminded that the electronic copy of their essay may be checked, at
any point during their degree, with Turnitin or other plagiarism detection software for
plagiarised material.
Word Count Date
Submitted
2

Table of Contents
Introduction......................................................................................................................................5
Task 1...............................................................................................................................................6
Task 1.1........................................................................................................................................6
Task 1.2........................................................................................................................................8
Task 1.3......................................................................................................................................10
Task 2.............................................................................................................................................12
Task 2.1......................................................................................................................................12
Task 2.2......................................................................................................................................14
Task 3.............................................................................................................................................15
Task 3.1......................................................................................................................................15
Task 3.2......................................................................................................................................17
Task 3.3......................................................................................................................................18
Task 4.............................................................................................................................................19
Task 4.1......................................................................................................................................19
Task 4.2......................................................................................................................................23
Conclusion.....................................................................................................................................27
References......................................................................................................................................28
Appendix........................................................................................................................................29
Table of Figures
Figure 1: RDBMS............................................................................................................................7
Figure 2: Network Model................................................................................................................9
Figure 3: Relational Database........................................................................................................10
Figure 4: Conceptual Diagram for Case Scenario.........................................................................14
Figure 5: ERD Diagram.................................................................................................................17
Figure 6: UML Diagram................................................................................................................18
Figure 7 phpmyadmin ERD...........................................................................................................19
Figure 8: Buyers Table..................................................................................................................20
Figure 9: Purchased Cars...............................................................................................................20
Figure 10: Cars..............................................................................................................................20
Figure 11: SoldCars.......................................................................................................................21
Figure 12: Sellers...........................................................................................................................21
Figure 13: MethodOfPayment.......................................................................................................22
Figure 14: Foreign Key SQL Queries............................................................................................22
3

Figure 15: DDL Inserting Command.............................................................................................23
Figure 16: DDL inserting data into database.................................................................................23
Figure 17: List of Entities..............................................................................................................23
Figure 18: Buyers..........................................................................................................................24
Figure 19: PurchasedCars..............................................................................................................25
Figure 20: Cars..............................................................................................................................25
Figure 21: SoldCars.......................................................................................................................26
Figure 22: Sellers...........................................................................................................................26
Figure 23: MethodOfPayment.......................................................................................................27
4

Introduction
Data modeling is defined as performing operations on database. This assignment is containing
designing of database for given case scenario. In Task 1, it is having introduction & comparison
between different database models by distinguishing top down approach & bottom down
approach and RDBMS & File based System. At task 2, it is having desiging of entities for case
scenario with conceptual diagram also. In task 3, it is having normalization technique for getting
better results for data tables and records. At task 4, it is having SQL queries which DDL and
DML commands for a database named as data modeling with a screenshot of the outcome.
5

Task 1
Task 1.1
Database
The database is defined as a collection of data or information which are well maintained and well
designed. This should be accessed by using an electronic medium that is a computer system.
Difference between RDBMS & File Management System
Table 1: Difference between RDBMS & File Management System
Attribute RDBMS File-Based System
Repetition of
Data
Redundancy of information is
very inferior.
This file system is having more
redundancy of information.
Security It is having high security with
different highly configured
security aspects.
It has fewer security aspects and
constraints are having very less security.
Inconsistency
of Data
Lower High
Centralization Yes No
Storing Data Structured Data by illustrating
constraints with their
relationship which are
interconnected.
Unstructured File (Data Pediaa.Com.
2018).
Advantages of RDBMS
 It is having the main aim for decreasing the redundancy or repetition and replication of
information or data from the database.
 It is offering a good level of physical data which are independence with completely
integrated data or information.
6

 It offers a multipurpose platform such that multiple users can visit and access their data
or information.
Disadvantage of RDBMS
 It requires more human technical effort, the meaning is that technically skilled or expert
is required.
 The complexity of hardware is there & expensive also.
Limitation of RDMS
 High Complexity
 Limits are having when you use data size
 Highly cost than file-based system
Figure 1: RDBMS
Advantages of File System
7

 We can easily take the back up of information or data at a very high speed of backing up.
 It very eases for making changes into the database or files with a feature of remote
accessibility.
 The efficiency of receiving information or data is very high.
Disadvantages of File System
 There is a redundancy of information or data is there with the inconsistency of it.
 It is having widely facing problems that are atomicity and accessing data continuously &
concurrently.
 Security & integration of data or information is also one of the major issues in this file
based system.
Limitation of File System
 Less Security Aspects
 Less Redundancy
 Less Inconsistency
Task 1.2
Advantages of Hierarchical System
 It very easy to handles a large amount of data.
 It has integrated information with completely independent information.
 Data is secured and simply modified information is there.
Disadvantages of Hierarchical System
 It is hard for implementation or applying this database and managing database.
 It is lacking by some industry standards and having complexity related to application
programming interface (Myreadingroom.co.in., 2019).
Advantages of Network Model
 It is very easy for designing this database as same as the hierarchical database.
 Accessibility of the database is there.
8

 It has more capability for handling & managing information or data with more than one
relationship between the entities.
Disadvantages of Network Model
 This model is having a large amount of complexity because it is having pointers for
managing data by pointers.
 CRUD operation required a big amount of shuffling of pointers (Arora K., 2014).
Figure 2: Network Model
Advantages of the Relational Model
 It is used for decreasing the redundancy or repetition and replication of information or
data from the database.
 It offers a multipurpose platform such that multiple users can visit and access their data
or information.
Disadvantages of the Relational Model
 It requires technical skilled or expert is required.
 The complexity of hardware is there & expensive also.
9

Figure 3: Relational Database
Task 1.3
Top-down Approach
The top-down approach is also known as analysis with designing. In this, we have divided
entities into sub entities until and unless the requirement is not completed.
Strengths
 By using this approach, it will provide less amount of completion time for a project or
task.
 It has designing techniques which are faster than others.
Weakness
 It is required that knowledge or technical person can apply this concept.
 Another major issue is that participation of task or user is very less.
Bottom down Approach
It is also known as the process of synthesis with designing. In this, first of all, information is
gathered and after that construction process take place with entities. After the completion of this,
the relationship takes place.
Strengths
 Giving straight & direct solution and answers with accuracy.
10

 It very eases for using this.
 For this, no highly or advance technical person is required.
Weakness
 Cost may be very high.
 Lower efficiency with less effectiveness due to less control of management (Andrew
Odendaal, 2017).
I would prefer a top-down approach for this case scenario for providing better databases
management with a good amount of efficiency and effectiveness of database managing
operation.
11

Task 2
Task 2.1
At below, I have mentioned entity name with their attribute as well as their data type
respectively. For this, case scenario I have used 6 entities and these are:
 Buyers
 PurchasedCars
 Cars
 SoldCars
 Sellers
 MethodOfPayment
Buyers
Table 2: Buyers
S.N. Name of Attribute for Entity Data Type of Attribute Constraints (if any)
1. BuyerId Integer Primary Key
2. BuyerName Varchar NOT NULL
3. BuyerContact Varchar NOT NULL
4. BuyerResidency Varchar NOT NULL
PurchasedCars
Table 3: PurchasedCars
S.N. Name of Attribute for Entity Data Type of Attribute Constraints (if any)
1. PurchaseId Integer Primary Key
2. PurchaseDate Date NOT NULL
3. BuyerId Integer Foreign Key
4. CarId Integer Foreign Key
5. PaymentId Integer Foreign Key
6. Price Varchar Foreign Key
12

Cars
Table 4: Cars
S.N. Name of Attribute for Entity Data Type of Attribute Constraints (if any)
1. CarId Integer Primary Key
2. ModelNumber Varchar NOT NULL
3. CarColor Varchar NOT NULL
4. Price Integer NOT NULL
5. CreationYear Date NOT NULL
6. Made Varchar NOT NULL
7. Mileage Integer NOT NULL
8. TypeOfBody Varchar NOT NULL
9. FuelTank Varchar NOT NULL
10. EngineSize Integer NOT NULL
11. ConsumptedFuel Integer NOT NULL
12. EmissionOfCO2 Integer NOT NULL
SoldCars
Table 5: SoldCars
S.N. Name of Attribute for Entity Data Type of Attribute Constraints (if any)
1. SoldId Integer Primary Key
2. SoldDate Date NOT NULL
3. SellerId Integer Foreign Key
4. PriceSold Integer NOT NULL
5. BuyerId Integer Foreign Key
Sellers
13

Table 6: Sellers
S.N. Name of Attribute for Entity Data Type of Attribute Constraints (if any)
1. SellerId Integer Primary Key
2. SellerName Varchar NOT NULL
3. ResidentialAddress Varchar NOT NULL
4. ContactInfo Varchar NOT NULL
MethodOfPayment
Table 7: MethodOfPayment
S.N. Name of Attribute for Entity Data Type of Attribute Constraints (if any)
1. PaymentId Integer Primary Key
2. InterestRate varchar NOT NULL
3. AmountPayble Integer NOT NULL
4. Type Varchar NOT NULL
Task 2.2
Figure 4: Conceptual Diagram for Case Scenario
14

Task 3
Task 3.1
Normalization
Normalization is a way of designing the database which is completely free from the repetition of
data or information for better execution of queries as well as database operation with a good
amount of efficiency.
Here, below is the step table for processing the normalization different rule that are UNF, 1 NF,
2 NF, and 3 NF.
Table 8: Table for Normalized Form
UNF 1 NF 2 NF 3 NF Name of Entity
Information
about Cars
BuyerId (PK) BuyerId (PK) BuyerId (PK) Buyers
Information
of Seller
BuyerName BuyerName BuyerName
Information
of Buyer
BuyerResidency BuyerResidenc
y
BuyerResidency
Information
on Purchased
Car
CarId (PK) PaymentId BuyerContact
Information
of Sold out
cars
PaymentId Type
Information
on Method of
Payment
Type InterestRate CarId Cars
InterestRate AmountPayble ModelNumber
AmountPayble CarId (FK) CarColor
Price
15

CreationYear
Mileage
CarId CarId TypeOfBody
ModelNumber ModelNumber FuelTank
CarColor CarColor EngineSize
Price Price ConsumptedFuel
CreationYear CreationYear EmissionOfCO2
Mileage Mileage
TypeOfBody TypeOfBody PurchaseId PurchasedCars
FuelTank FuelTank PurchaseDate
EngineSize EngineSize BuyerId (FK)
ConsumptedFuel ConsumptedFu
el
CarId (FK)
EmissionOfCO2 EmissionOfCO
2
PaymentId (FK)
Price
SellerName PurchaseId
ResidentialAddress PurchaseDate
ContactInfo BuyerId (FK) SellerId Sellers
CarId (FK) CarId (FK) SellerName
SoldDate PaymentId
(FK)
ResidentialAddres
s
SoldPrice Price ContactInfo
SellerId
SellerId SoldId (PK) SoldCars
SellerName SoldDate
ResidentialAdd
ress
SellerId (FK)
ContactInfo PriceSold
SoldId (PK) BuyerId (FK)
16

SoldDate
SellerId (FK) PaymentId MethodOfPaymen
t
PriceSold InterestRate
BuyerId (FK) AmountPayble
Type
Task 3.2
Figure 5: ERD Diagram
17

Task 3.3
Figure 6: UML Diagram
18

Figure 7 phpmyadmin ERD
19

Task 4
Task 4.1
Buyers Table
Figure 8: Buyers Table
PurchasedCars
Figure 9: Purchased Cars
Cars
20

Figure 10: Cars
SoldCars
Figure 11: SoldCars
Sellers
Figure 12: Sellers
MethodOfPayment
21

Figure 13: MethodOfPayment
Foreign Key Constraint SQL Queries
Foreign Key is used for connecting more than one table for using and sharing their attribute for
getting better results.
Figure 14: Foreign Key SQL Queries
Insertion Commands
22

Figure 15: DDL Inserting Command
Figure 16: DDL inserting data into database
Figure 17: List of Entities
23

Task 4.2
At below, there are information or data is displayed by using the select command with a database
for the above mentioned entities.
Buyers
Figure 18: Buyers
PurchasedCars
24

Figure 19: PurchasedCars
Cars
Figure 20: Cars
25

SoldCars
Figure 21: SoldCars
Sellers
Figure 22: Sellers
26

MethodOfPayment
Figure 23: MethodOfPayment
27

Conclusion
We have successfully completed the designing of the database for a given case scenario after the
completion of this task. In Task 1, we have learned about introduction & comparison between
different database models by distinguished between top-down approach & bottom down
approach and RDBMS & File based System. At task 2, we have designed the entities for case
scenario with conceptual diagram also. In task 3, performed normalization technique for getting
better results for data tables and records. At task 4, successfully executed SQL queries which
DDL and DML command for a database named as data modeling with a screenshot of the
outcome.
28

References
Arora, K. (2014). Network model and their Advantages and Disadvantages | Data models.
[online] http://dbmsenotes.blogspot.com. Available at:
http://dbmsenotes.blogspot.com/2014/03/comparison-of-data-models-data-models.html
[Accessed 2 Mar. 2019].
Myreadingroom.co.in. (2019). Hierarchical Data Model in DBMS. [online] Available at:
http://www.myreadingroom.co.in/notes-and-studymaterial/65-dbms/468-hierarchical-data-
model.html [Accessed 2 Mar. 2019].
Pediaa.Com. (2018). Difference Between File System and RDBMS in Tabular Form -
Pediaa.Com. [online] Available at: http://pediaa.com/difference-between-file-system-and-
rdbms-in-tabular-form/ [Accessed 2 Mar. 2019].
Andrew Odendaal. (2017). Top-down vs Bottom-up Database Design ~ Andrew Odendaal.
[online] Available at: https://ao.gl/top-down-vs-bottom-up-database-design/ [Accessed 2
Mar. 2019].
29

Appendix
30