Database Systems Assignment: ER Diagrams, Relational Models and Design

This document presents a comprehensive solution to a DBMS assignment, addressing key concepts in database management. The assignment covers topics such as the differences between logical and physical data independence, the key components of a DBMS (including software, hardware, data, procedures, data managers, and data dictionaries), and the definitions of domain, attribute, tuple, and relation in the relational model. The solution also includes an ER diagram illustrating entities, attributes, and relationships, along with DDL statements for table creation. Furthermore, the assignment delves into subclass relationships in ER diagrams, discussing insertion and deletion rules, and differentiating between disjointed, overlapping, and union subtypes. This resource provides a detailed understanding of database design principles and implementation.

DBMS

Table of content
QUESTION 1...........................................................................................................1
QUESTION 2...........................................................................................................2
QUESTION 3...........................................................................................................2
REFERENCES........................................................................................................3

QUESTION 1
a) Explain difference between logical and physical data independence
Logical Physical
In this conceptual schema can be changed
without changing its structure.
In this conceptual schema can not be changed
without changing its structure.
It only provides protection to application
program.
It provides independence and protection to
conceptual and external schema.
Modification are done to change structure. The modifications are done to improve
performance.
b) Describe key components of DBMS and their interactions
Software- They are set of programs that help in managing and controlling of database. It
includes operating system, data applications, etc.
Hardware- They are physical devices which act as interface between user and computers. It
contains CPU, server, I/O devices, etc. (Cuomo and Moore, 2018)
Data- It is the raw data that is processed, stored, collected, etc. in a database.
Procedure – They are instruction and guidelines that how to use, manage and operate database.
Data manager- It helps in managing of data in database and providing recovery of data in case
of failure of system.
Data dictionary – It contains information about database itself. It ensures that data flows
through defined process.
c) Explain what is domain, attribute, tuple and relation in relational model?
Domain – It is set of values which is used to model data.
Attribute – The columns in a table represents attribute.
Tuple – It is a single row in table that contain single record is called a tuple.
Relation – It describe the relation of specific attribute with another in database.
d) What solutions can be applied?
Yes, there are certain issues in this. So, in order to solve it first of all primary key has to
remove from middle name.
1

QUESTION 2
ER Diagram
In this ER diagram there are four entities which are product, buyers, vendors, components.
All these entities has some attributes defining the entity. When designing database schema these
entities will be converted into database tables using specific DDL statements such as: Create
table DDL statement will be used to create all the four tables. While creating tables all the
primary keys of specific tables will be specified and the foreign key used in it will also be
specified. For example:
CREATE TABLE PRODUCT (UNIQUE_ID NUMBER DEFAULT (10), PRODUCT_NAME
VARCHAR(20), PRICE VARCHAR(20), VENDOR_NAME VARCHAR(20), PRIMARY
KEY (UNIQUE_ID));
QUESTION 3
a) Give three reasons why subclass relationships are a necessary addition to E-R diagrams
 It is because it helps in identifying associations among several entities.
 It is easy to develop relationship with entity in E R diagram (Zheng and Bai, 2018)
 It clearly shows which attribute is related to another and in what way.
b) Describe the insertion/deletion rules that would need to be applied to the super classes
Insertion rules – insert by changing table attributes.
2

Deletion rules – By deleting specific table and entity.
c) Explain the difference between disjointed, overlapping and union and what this means in
the computer systems diagram
Disjointed Overlapping Union
It indicates that entity
of super type is only
member on one
subtype.
Here, entity can be a
member of both sub
type.
In this entity can hold
any data type.
3

REFERENCES
Books and Journals
Cuomo, G.A. and Moore, V.S., International Business Machines Corp, 2018. Database
management system based on a spreadsheet concept deployed in an object grid. U.S.
Patent Application 10/031,904.
Zheng, Y. and Bai, Q., 2018, February. Design and Implementation of Database Centralized
Access Control and Audit System. In 2018 International Conference on Computer
Science, Electronics and Communication Engineering (CSECE 2018). Atlantis Press.
4