[go: up one dir, main page]
Scribd
Upload
39 views14 pages

COMP1845

The document outlines the design and implementation of a relational database system for London Autos, detailing the elicitation process, identified requirements, and database normalization up to the third normal form (3NF). It includes specific entities such as Car, Customer, Sales Agent, and Finance, along with their attributes and relationships. Additionally, it provides examples of queries to retrieve sales and finance details.

Uploaded by

duydkgcd230010
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
39 views14 pages

COMP1845

The document outlines the design and implementation of a relational database system for London Autos, detailing the elicitation process, identified requirements, and database normalization up to the third normal form (3NF). It includes specific entities such as Car, Customer, Sales Agent, and Finance, along with their attributes and relationships. Additionally, it provides examples of queries to retrieve sales and finance details.

Uploaded by

duydkgcd230010
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 14

University of Greenwich ID Number: 001418655

FPT Student ID Number: GCD230010

Module Code: COMP1845

Module Assessment Title: Introduction to Computer Science and its


Applications

Lecturer Name: Mr. Pham Thanh Son

Submission Date: 5/12/2024


Coursework
Part 1 (Design and Implementation of Database System)

a.

Process of Elicitation

To grasp London Autos requirements for the relational database fully, the
following steps were taken:

- Scenario Analysis: Analysed the given scenario to identify key entities,


attributes, and processes.
- Stakeholder identification: All important stakeholders, like business owners,
sales agents, and customers, were identified, and their functions in the
system were documented.
- Assumptions: Listed potential gaps in the scenario by making reasonable
assumptions - for example, traditional methods of payment, types of car
accessories.

Identified Requirements from the Scenario:

1. Car Management
- Store information about cars, including name, model number, description,
make, manufacturing date, and price.
- Keep track of whether a car is new or returned for resale.
- Manage customer financing information (whether paid directly or financed).
2. Customer Management
- Maintain details about customers, including personal information, purchase
history, and financial options.
3. Sales Agent Management
- Store agent information (name, ID, contact details) to manage sales activities.
4. Accessory Management
- Store accessory details (e.g., seat covers, wheel covers).
5. Transaction and Sales Records
- Track each sale (cars and accessories) and associate it with customers and
sales agents.
- Handle both new and used car sales with payment type (cash or financing).

Entities Identified for the Database:

● Car: Stores information about each car.


● Accessory: Stores accessory details.
● Customer: Stores customer information.
● Sales_Agent: Stores sales agent information.
● Sales: Records transactions (car sales).
● Finance: Records financing details, if applicable.

b.

c.

1st Normal Form (1NF)

A relation is in 1NF if:

● Each column contains atomic values.


● Each row is unique, with a primary key.

Application in the Scenario:

● All tables in the diagram adhere to atomicity (no repeating groups or


multi-valued attributes).
● Primary keys are defined for each table: car_id, customer_id, agent_id,
sales_id, finance_id, accessory_id.

2nd Normal Form (2NF)

A relation is in 2NF if:

● It is in 1NF.
● All non-prime attributes are fully functionally dependent on the primary key (no
partial dependency).

Application in the Scenario:

● In the Sales table:


○ Non-prime attributes like date_of_sale and payment_type depend
entirely on the composite key (sales_id), so there is no partial
dependency.
● In the Finance table:
○ bank_name, amount, and interest_rate are dependent on the
finance_id, not partially on sales_id.

Thus, the schema satisfies 2NF.

3rd Normal Form (3NF)

A relation is in 3NF if:

● It is in 2NF.
● There are no transitive dependencies (non-prime attributes are not dependent
on other non-prime attributes).

Application in the Scenario:

● Customers table:
○ Attributes like first_name, last_name, and contact depend only on the
primary key (customer_id), with no transitive dependencies.
● Sales table:
○ date_of_sale and payment_type depend only on sales_id.
● Finance table:
○ bank_name, amount, and interest_rate depend directly on
finance_id with no transitive dependencies.

All tables are in 3NF as there are no transitive dependencies.


d.

Car Table:

Customer Table:

Sales Table:

Sales Agent Table:

Accessory Table:
Finance Table:

e.

Car Database:
Customer Database:

Sales Agent Database:


Sales Database:

Finance Database:
Accessory Database:

f.

1. Query 1: Get all sales with customer and car details.


2. Query 2: Get finance details for all financed sales.

Part 2 (Data Manipulation)

a.
b.

c.
d.

e.
f.

g.
h.

You might also like