Database Systems and Design Project: Oracle DBMS Implementation

This document presents a comprehensive database design project centered around a manufacturing company scenario. It begins with selecting a specific company situation and designing a database, followed by the creation of an Entity Relationship (ER) diagram illustrating entities, attributes, and relationships, including weak and strong entities. The project then delves into logical database design, creating database tables using Oracle DBMS, and creating indexes to improve database performance. Data population strategies are explored, and SQL query language is utilized to retrieve and manipulate data. The assignment covers various aspects of database design, implementation, and query construction, providing a practical understanding of database management systems within a business context. The project utilizes Oracle DBMS to design tables, indexes, and relationships between entities. This project covers logical design, table creation, data population, and SQL queries. The assignment showcases a detailed analysis of database systems and design principles.

Database Systems and Design

Table of Contents
INTRODUCTION...........................................................................................................................3
TASK 1............................................................................................................................................3
1.1 Select the specific situation based on the company and design the database design ............3
1.2 Create Entity relationship diagram and shows all entities, attributes and weak or strong
entity. ..........................................................................................................................................4
TASK 2............................................................................................................................................5
2.1 provide logical database design according to the scenario....................................................5
2.2 Create database tables using Oracle DBMS..........................................................................6
Department table-.........................................................................................................................7
2.3 create most useful indexes on the tables..............................................................................11
2.4 Data population....................................................................................................................12
2.5 SQL Query language............................................................................................................15
CONCLUSION..............................................................................................................................21

INTRODUCTION
Database management system is basically the collection of information that store in the
form DBMS. It allows for manipulate, retrieve and store the data within the database system.
This report will discuss about the database that design by using oracle DBMS. This tool is
designed for the data that contain the transaction management, schema objects and relation
between the entities. This assignment will describe about the logical scenario of company to
manage their business operations and functions with the help of database management system.
TASK 1
1.1 Select the specific situation based on the company and design the database design
Company scenario-
According to scenario, it is used the manufacture organization which based on the
manufacture company to sell their product and service in marketplace. Each and every
department has performed their specific task to maintain the facilities in proper manner. All the
employee connected to the project and work on particular task in proper manner (Abadi and
Faleiro, 2018). On the other hand, Mechanics and sales man has been connected with the product
of company and both performed different task whereas mechanics repair the car or salesman sell
the car in marketplace. This entire operations and function is managed with the help of database
system.
Relevant background information in the organization:
A manufacture company is performed different operations and functions such as selling
the product and repair car in proper manner. Each and every employees are connected with the
department plays different role in proper manner. The basic aim of this firm to provide the
facilities to their customer to repair their car in proper manner.
Operation supported-
It supported to sell the products, maintenance of car etc.

1.2 Create Entity relationship diagram and shows all entities, attributes and weak or strong
entity.
Entity relationship diagram is basically the data modelling technique that representing the
information with the help of different entities and their specific relationship between them. It is
the conceptual model that useful in the entity framework infrastructure. There are different types
of element applicable in the ER.
 Entities
 Relationship
 Attributes
According to the company scenario, it can be used the different entities in the database tables.
Entities Attributes
 Employee  emp_id (PK)
 emp_name
 emp_age
 emp_city
 Department  Dept_id(PK)

 Dept_name
 Dept_location
 car  Car_Year
 car_Model
 Car_Manufacture
 Salesman  salesman_id(PK)
 salesman_Name
 Dependent  dependent_Name
 dependent_sex
 dependent_DOB
 dependent_Relationship
 Mechanic  mechanic_id(PK)
 name
 Project  project_id(PK)
 project_name
 project_location
Weak entity- In relational database system, A weak entity is basically the entity that
cannot be uniquely identified. It also used as a foreign key in the conjunction with their attributes
to create primary key (Felemban and et.al., 2018). According to the case study, dependent is
generally weak entity that cannot has any unique identified in the database tables.
Employee= strong entity
Strong entity- This entity is based on the independent and unique. It cannot be dependent
on any other entity. For Example- Employee is a strong entity that contain the strong and
independent in the database system.
Dependent= weak entity
Generalisation- Generalisation is basically a process that extracts to share the
characteristics form two or more classes. It also combination of generalized superclass. For
Example- According to ER diagram, it shows the generalisation between the employee,
mechanics and salesman. All the entities are connected to create ISA relationship between them.

Specialisation- It is a type of process that can be divided the entities into the sub entities.
It is also known as top down process to split into the different entity in proper manner.
Specialisation is mainly increased the size of data schema and also splits the entity into the
multiple new entities that inherit the features to another entities.
TASK 2
2.1 provide logical database design according to the scenario
Logical database design is necessary for organization to handle the business operations
and function in proper manner. It is the first step for design the data modelling that considered to
the high level abstract design (Groomer and Murthy, 2018). The basic aim of this phase to
contain the information and data in the database system such as entities: department, employee,
dependent etc. these are different information contain in the database system. Afterwards, it will
create relationship between the different data items that used in the database design. The
relationship and constraints are expressed by using the concepts that provided through the level
of data model.
 It is provided the flexible data structure capabilities and abilities.
 It contains the data that store in the DBMS. A database is independent regardless of database by using the logical concepts.
Primary key- Foreign key- Non- key attribute-
Primary key is basically
known as unique key that
applicable in database to create
relationship with another table.
It can be stored the unique
identifier record such as
employee_id, depart_id etc. A
relational database must be
creating by using one primary
key.
This key is basically used for
link two tables and also
applicable as a reference key
into another table. For
Example- in Employee_table,
emp_id is working as a foreign
key into the department table.
It is not part of any type of
key. It is the simple description
and divided into the category.
For Example- emp_name,
emp_age, emp_id, emp_no etc.
All these are the non key
attributes because it cannot be
used as key in the database
tables.

Logical relationship between the database entities by using primary key, foreign key in tables:

2.2 Create database tables using Oracle DBMS
Employee table-

Department table-
Mechanic table-

Project table
Salesman table-

Dependent table-

Car table-
Connection name = database11_XE
user name = database11
password = 123456789
Steps-
First of all, import the SQL file into the Oracle SQL developer.
It is required to create to connection and use this user name and password.

2.3 create most useful indexes on the tables
An index is based on the schema object that contain each and every value in the column
of database table. It also provides the direct and access the rows in fast manner. In Oracle

database, Each and every table has created the own unique index with the help of primary key in
by default. It doesn't need for user to create the index but sometimes, it is required for database
system that created one or more column, attributes in the tables. Indexes are created in the
database because it helps for search engine for increased the speed up and it is simply put the
index as pointer to data in the table.
2.4 Data population
A data population is a based on the computer programming that include the command for
permitting the user to manipulate the data in the database system (Li and et.al., 2018). It also
included the insertion of data in the form of tables and also performing different operations such
as delete, retrieving, update and modify the existing data. It is the mostly incorporated in SQL
databases.
According to case study, it can be used the data manipulation that organized the following
commands as followings:
 Select- This command is basically used for retrieve the rows from the tables.
 Update- this command is modified the data from one record to another.
 Insert- it is basically used for add one and more records in the database tables.
 Delete- It is also removed the records in the database table by using the specific
conditions.

2.5 SQL Query language
Structural query language refers to computer programs that represented the request,
update, retrieve the information or data from the database system. It is also sending the data with
the help of queries (Pei and et.al., 2018). It is generally created for accessing, modifying data out
or in from database management system. For Example- the employee retrieve all the
information, record of table with the help of query language.
Condition for inner join-

Outer join:-

Use count and mathematical expression

Sorting and ordering

Generate Minimum value Conditions in SQL

CONCLUSION
As per discussion, it is successfully designed the database system of company in effective
ways. It also used the oracle database to design the tables, indexes and relationship between
them.

REFERENCES
Books and Journals:
Abadi, D.J. and Faleiro, J.M., 2018. An overview of deterministic database
systems. Communications of the ACM, 61(9), pp.78-88.
Felemban, M. and et.al., 2018. Design and Evaluation of A Data Partitioning-Based Intrusion
Management Architecture for Database Systems. arXiv preprint arXiv:1810.02061.
Groomer, S.M. and Murthy, U.S., 2018. Continuous auditing of database applications: An
embedded audit module approach. In Continuous Auditing: Theory and Application (pp.
105-124). Emerald Publishing Limited.
Li, G., and et.al., 2018. Crowdsourcing Database Systems and Optimization. In Crowdsourced
Data Management (pp. 71-95). Springer, Singapore.
Pei, J. and et.al., 2018. Database Systems for Advanced Applications. Springer.