MITS4003
Database SystemsAssignment Summary
This study source was downloaded by 100000852006834 from CourseHero.com on 05-30-2023 05:31:29 GMT -05:00
https://www.coursehero.com/file/146369019/MITS4003docx/
Table of Contents
Introduction……………………………………………………………………………………………………………………………….3
Business Case Study……………………………………………………………………………………………………………………3
Entity Relationship Diagram (ERD)………………………………………………………………………………………………4
Relational Schema………………………………………………………………………………………………………………………4
Supplementary Design Requirements…………………………………………………………………………………………….5
Physical Design & Assumptions……………………………………………………………………………………………………6
References…………………………………………………………………………………………………………………………………..
Figure 1: Entity Relationship Diagram (ERD)…………………………………………………………………………………4
Figure 2: Relational Schema…………………………………………………………………………………………………………5
Y
Table 1: Customer’s Table Attributes……………………………………………………………………………………………..5
Table 2: Vehicle’s Table Attribute………………………………………………………………………………………………….6
Table 3: Rental’s Table Attribute……………………………………………………………………………………………………6
Table 4: Physical Design……………………………………………………………………………………………………………..7
1
This study source was downloaded by 100000852006834 from CourseHero.com on 05-30-2023 05:31:29 GMT -05:00
https://www.coursehero.com/file/146369019/MITS4003docx/
Introduction
This assignment is based on the implementation of a database system and a case study is given in
this assignment for the completion of this assignment. The basic aim of this assignment is to
evaluate the basic knowledge related to the database management system (DBMS) for the realworld software business requirement. This assignment will give the knowledge of developing an
enterprise data model that can easily reflect the business rule and basic fundamental rules of the
organization. The development of an enterprise data model includes the implementation of
entities, table architecture, relationships, and multiple developing rules. The development of
physical relational based data integration uses to provide the complete relations between the
tables and entities in terms of defining primary key (PK) foreign key (FK) and many more.
Business Case Study
In this case study, a vehicle rental company whose name is Prestige Automobile Rental (PAR) is
given. This company uses to provide old vehicles on rent to their customers. Previously this
organization uses to manage its database using a manual excel based system. Now, this is moving
into the technological-based component and wants to build a complete database management
system (DBMS). After developing the database the rental process will be very easy and the
customer can also easily perform their required section. In this case study, some basic
information is also provided related to the business process or business rules of this organization.
And as per that rules, the ERD and relation schema of this company will be created. Four
outcomes need to be derived like an entity-relationship diagram (ERD), relation schema,
supplementary design requirements, and physical design discussion also. At the end of this
assignment report, all this possible outcome will effectively be derived.
2
This study source was downloaded by 100000852006834 from CourseHero.com on 05-30-2023 05:31:29 GMT -05:00
https://www.coursehero.com/file/146369019/MITS4003docx/
Entity Relationship Diagram (ERD)
ER diagram is a high-level conceptual data model diagram. In the process of the database
management system (DBMS) the development of an entity-relationship diagram (ERD) is very
important because this diagram helps to analyze the requirements of data in a systematic way to
generate a well-designed database architecture system. The entity-relationship diagram (ERD)
effectively represents real-world entities and there are a lot of relationship methods that are also
available. Any business case should be identified effectively by creating an entity relationship
diagram (ERD). All basic data requirements used to be analyzed in the implementation of an
entity-relationship diagram (ERD). For this PAR organization, three different tables will be
created as per their business case studies like customer table, vehicle table, and rental table. The
entities and attributes of all tables will directly be connected and associated to each other in such
a manner so that the data can easily be retrieved for this rental vehicle system. The ERD diagram
for PAR business case study is given below:
Figure 1: Entity Relationship Diagram (ERD)
In this diagram, three different tables are given customer, vehicle, and rental. Multiple entities
are also available for every single database table. Entities for customer table are customer id,
name, address, mobile, DL number, credit card number, and the date and demerit point. For
vehicle table entities are vehicle id, vehicle type, registration number, model number, distance
3
This study source was downloaded by 100000852006834 from CourseHero.com on 05-30-2023 05:31:29 GMT -05:00
https://www.coursehero.com/file/146369019/MITS4003docx/
traveled, current condition, and the vehicle is available or not. For the rental table, the entities are
rental id, customer id, vehicle id, charges, and return date.
Relational Schema
In the implementation of a database management system relational schema use to design and
structure the relationship between the databases table, entities, and their attributes also. This is a
table, column, and relationship that is used to associate or link with multiple components in a
database management system. The creation of relational schema helps to understand the structure
of a database and for a new database, this can easily identify if there is a need for some changes
or not. Some aspects are also available like:
Data should be stored in the database structure.
The relationship between the data elements should be determined effectively.
The logical structure should also be defined in the DBMS architecture.
Figure 2: Relational Schema
This image is about the relational schema of this vehicle renal company and as per the ERD
diagram this relational schema has been created. This relational schema clearly defining the
involvement of primary key and foreign key along with establishing a relationship between them.
4
This study source was downloaded by 100000852006834 from CourseHero.com on 05-30-2023 05:31:29 GMT -05:00
https://www.coursehero.com/file/146369019/MITS4003docx/
Supplementary Design Requirements
In this section data attribute, the information will be given such as:
Customer Table:
Attribute Name Attribute Type
Cus_id Int
Cus_name Varchar
Cus_mobile Varchar
Cus_DL_no Varchar
Cus_credit_card_no Nvarchar
Cus_added_date Date
Cus_demerit_point Int
Table 1: Customer’s Table Attributes
Vehicle Table:
Attribute Name Attribute Type
V_id Int
V_type Varchar
V_reg_no Varchar
V_no Varchar
V_model_no Varchar
V_vin_no Varchar
V_distance_travelled Decimal
V_current_codition Varchar
V_is_available Bit
Table 2: Vehicle’s Table Attribute
Rental Table:
Attribute Name Attribute Type
R_id Int
Cus_id Int
V_id Int
R_charges Decimal
Return_date Date
Table 3: Rental’s Table Attribute
5
This study source was downloaded by 100000852006834 from CourseHero.com on 05-30-2023 05:31:29 GMT -05:00
https://www.coursehero.com/file/146369019/MITS4003docx/
Physical Design & Assumptions
Physical design in the database system is used to optimize performance while ensuring data
integrity and the replacement of unnecessary data. A physical design process is given an
enhancement to transferring entities into tables and attributes into columns.
Tables Columns Data Types Notes
Customer Cus_id
Cus_name
Cus_mobile
Cus_address
Cus_DL_no
Cus_credit_card_no
Cus_added_date
Cus_demerit_point
Int
Varchar (100)
Varchar (10)
Varchar (max)
Varchar (50)
Varchar (30)
Date
Int
Primary Key
Vehicle V_id
V_type
V_reg_no
V_no
V_model_no
V_vin_no
V_distance_travelled
V_current_codition
V_is_available
Int
Varchar (30)
Varchar (50)
Varchar (50)
Nvarchar (50)
Nvarchar (50)
Decimal (10,2)
Nvarchar (50)
Bit
Primary Key
Rental R_id
Cus_id
V_id
R_charges
Return_date
Int
Int
Int
Decimal (10,2)
Date
Primary Key
Foreign Key
Foreign Key
Table 4: Physical Design
6
This study source was downloaded by 100000852006834 from CourseHero.com on 05-30-2023 05:31:29 GMT -05:00
https://www.coursehero.com/file/146369019/MITS4003docx/
7
This study source was downloaded by 100000852006834 from CourseHero.com on 05-30-2023 05:31:29 GMT -05:00
https://www.coursehero.com/file/146369019/MITS4003docx/
References
Tomoda, A., Isoda, Y. and Ushijima, K., Hitachi Ltd, 2019. Database management
system and method. U.S. Patent 10,366,075.
Graham, D., and Ravanbakhsh, S., 2019. Equivariant Entity-Relationship
Networks. arXiv preprint arXiv:1903.09033.
Irshad, L., Yan, L., and Ma, Z., 2019. Schema-Based JSON Data Stores in Relational
Databases. Journal of Database Management (JDM), 30(3), pp.38-70.
8
This study source was downloaded by 100000852006834 from CourseHero.com on 05-30-2023 05:31:29 GMT -05:00
https://www.coursehero.com/file/146369019/MITS4003docx/
Powered by TCPDF (www.tcpdf.org)