Database Systems: Database Design and Implementation - [Course Code]

This document presents a comprehensive solution to a database design and implementation assignment. It begins with an introduction and proceeds to Task 1, which involves constructing data models according to business rules, illustrating entities, attributes, primary keys, and foreign keys. Task 2 focuses on normalization, specifically explaining 3NF and its role in efficient database design. Task 3 covers the creation of Data Definition Language (DDL) for various database tables. Task 4 provides screenshots of the database tables and record insertion. Finally, Task 5 offers solutions to several SQL queries, including finding co-owned accounts, listing accounts with personal loans, retrieving department and branch information with manager names, and identifying managers with salaries above average. The document thoroughly addresses each aspect of the assignment, providing clear explanations and practical examples.

DATABASE SYSTEMS DATABASE
DESIGN & IMPLEMENTATION

TABLE OF CONTENTS
INTRODUCTION...........................................................................................................................3
TASK 1............................................................................................................................................3
Construct data models according to the business rules and showing primary key, foreign key,
entity and attributes................................................................................................................3
TASK 2............................................................................................................................................5
Normalization.........................................................................................................................5
TASK 3............................................................................................................................................6
Create Data definition language of database table.................................................................6
TASK 4..........................................................................................................................................15
Screenshot of database table and insert record into the table...............................................15
1. ....................................................................................................................................................15
TASK 5..........................................................................................................................................16
Solve queries........................................................................................................................16

INTRODUCTION
TASK 1
Construct data models according to the business rules and showing primary key, foreign key,
entity and attributes.
Entity- It is a real world entity of particular object in the form of entities. In database
management system, all data are stored in the form of table containing information such as
customer, account, bank, debit card, department and loan etc.
Attributes- Attributes is an instance of data in database system. Attribute is quality of
characteristic of particular person and place.
Primary key-This is main concept that specify the relationship between tables and
columns. It can be designed for representing unique identity in database table.
Foreign key-It is a group of data in a table that is relational database and link between
two tables.
Data Model Diagram

Figure 1: Data Model

TASK 2
Normalization
Normalization is a process or technique which help in reducing large table into smaller table. It
will generate dependency of data and reduces redundancy.
Figure 2: 3NF
3NF is a step of normalization which assist for building an effective database effectively
and efficiently.
According to this table, it can be concluded that customer_id storing the information and
details of customers and branch_id is storing the detail and information of branch and bank
information. Branch holds the customer information. In case, there is a need to link between two
tables by using foreign key. This key assist in creating relationship between tables.
In customer table, Branch_id is pointing or reference corresponding column in customer
table. It is store the branch id value in customer database table. It will show the data dependency.

Suppose, Customer wants the branch information so that they will determine branch id
for particular customer while customer detail already save in branch.
In Account table, this data base table store details of accounts such as account_name,
account_id, account_balance, account_number etc. It is required for customers to mention
customer_id in particular account.
According to account table, it can be concluding that Customer_Id is storing in Account
table so it can easily determine particular customers’ details of their id’s.
TASK 3
Create Data definition language of database table
Account database table:

Branch table:-

Customer table-

Debit Card Table-

Department database table-

Employee database table-

Loan table-