Database Management System Assignment Solution for Data Science

This document presents a comprehensive solution to a Database Management System (DBMS) assignment, suitable for data science students. The assignment explores the core concepts of relational databases, including data structures, attributes, tuples, rows, columns, and tables, along with the concept of views and their advantages. It contrasts the features of Relational Database Management Systems (RDBMS) and Non-Relational DBMS, highlighting their structural differences, query languages (SQL vs. flexible data models), and storage methods. The solution includes practical examples of Data Manipulation Language (DML) codes for creating, inserting data into tables for staff, books, students, and borrowing. Furthermore, it demonstrates SQL integrity features and relational algebra operations, providing detailed outputs and results for each operation. The document concludes with a list of references supporting the content.

Running head: DATABASE MANAGEMENT SYSTEM
Database Management System
Name of student
Name of University
Author’s Note

1DATABASE MANAGEMENT SYSTEM
Table of Contents
1. Concept of Relational Database...................................................................................................2
a) Relational Data Structure:.......................................................................................................2
b) About Attributes and Tuples:..................................................................................................2
c) Rows, Columns and Tables:....................................................................................................3
d) View:.......................................................................................................................................4
2. Features of a Relational and Non-Relational Database Management System:...........................5
3. (a) DML codes to create:............................................................................................................8
(b) DML codes to insert:............................................................................................................10
(c) SQL integrity features:.........................................................................................................11

2DATABASE MANAGEMENT SYSTEM
1. Concept of Relational Database
a) Relational Data Structure:
Figure 1: Relation Diagram
Source: Author
b) About Attributes and Tuples:
In the Student Relation of figure 1, tuple is a row. Here, it has 5 tuples with 6 values that is
called as 6 tuples. In general, n- tuples has a list ordered of values n. If t1 is refer to first tuple
variable for the relation STUDENT, then t1 = <S10010, Chan Wai Yee, 56A, Jalan 2/14, Taman
Midah, kuala Lampur, 012-3256780, F, [e-mail]>, similarly for the other. Tuples may
appear in sorted or unsorted order. The relations of the order remain the same and give same
information or meaning.

3DATABASE MANAGEMENT SYSTEM
Attribute is the relation characteristic of a column. In figure 1, the relation of STUDENT
consists of 7 attributes namely, id (the student ID), sName, Address, State, Contact Number, Sex,
Email.
Similarities
The relation tuple are similar to the file records, and attributes are field that are analogues
(McGinnes and Kapros 2015).
Difference
The model of relational database is logically related to tables that contain data. Attribute is
a column were particular kind of data are kept; tuple is a row that holds the particular event or
entity of the relation data.
c) Rows, Columns and Tables:
In a relational database concept, tables have a rows and a column. In the table, each of the rows
will represent the related values of the collection (Keith et al. 2018). The information of the object
is hold in the database to represent in the table. In any specific table, data has one kind of objects
called the entities. Table is actually a concept of the data and relation is the concept of relational
model.
Now in a table each of the rows represents one object called the entity that is the collection of the
related values. If the diagram of figure 1 is considered, it has the data of one customer in one of the
row in Student table. Thus, row has considered as a database concept and tuple as the concept of
relational model.

4DATABASE MANAGEMENT SYSTEM
Additionally, in a table each of the columns holds some kind of data. Each of the data is describe
by some column name. For example, in the student table there are sName, Address, State and
many more. Thus, column has considered as a database concept and attribute as the concept of
relational model.
d) View:
A View is a set of queries for virtual tables that holds data from one or more tables and
stored in a database in the form of an object (Learning Views In SQL Server 2018). This has done
for the security purpose that restricts the user to view certain columns and rows. It is better
explained in the below given example:
Figure 2: Example of View
Source: (c-sharpcorner 2018)
Advantages of Views

5DATABASE MANAGEMENT SYSTEM
Independence of logical data: The database tables and application can be independent
through view.
Data integrity: DBMS checks the data has accessed through view that ensures to meet
the constraints of the specified integrity.
Consistency: In the database, an unchanged, consistent image can be present by view for
the structured database.
Structural simplicity: the user can make sense of the personalized view of the structure
of the database that has presented through a set of virtual tables.
Query Simplicity: Queries of multiple tables can be drawn into single table through view
Security: The user can access the database through an authorized set of views that contain
some specific data.
2. Features of a Relational and Non-Relational Database Management System:
Features RDBMS Non-RDBMS
1. They are structured in a
very organized way.
Example, phone book in
which addresses and phone
numbers are stored
They are distributed and
document-oriented.
Example, A file folders that
has everything from phone
number to address of a
person, likes on facebook
and preferences of online

6DATABASE MANAGEMENT SYSTEM
shopping
2. SQL (Structured Query
Language) is used to query
and maintain the database
Data models are Flexible
3. Data provided are stored in
the tables
Types of databases for
NoSql are: Key-Value
Store,
Column-based Store,
Document-based Store-It,
Graph-based
4. Data are stored in the form
of rows and columns
The Key-Value Store has
Tables with Big Hash.
Example Amazon S3,
Riak.
5. It provide a primary key
facility to identify the row
uniquely
Each block of the storage of
Column-based Store data
from only one of the
column. Example, HBase,
Cassandra
6. For a quick retrieval of the
data, indexes are created
The Document-based Store
is a database that stores
tagged elements type of
documents

7DATABASE MANAGEMENT SYSTEM
7. Shares common column
through primary and foreign
key in more than two tables.
The Graph-based is a
network database. Data are
stored and represented using
nodes and edges
8. It ensures the compliancy of
ACID( Atomicity,
Consistency, Isolation,
Durability)
Data are easily spread to
multiple servers using
storage and cloud
computing.
Development are rapid
9. The data is unchanging and
structured
It often have no structure or
little structure and the data
that are store contains large
volumes.
10. Examples of available
database in market
MySQL, Oracle,
MA SQL Server,
IMB DB2,
Microsoft Azure,
Sybase,
MarlaDB, PostgreSQL
MongoDB,
Apache’s CouchDB,
HBase,
Oracle NoSQL,
Apache’s Cassandra DB,
Riak

8DATABASE MANAGEMENT SYSTEM
3. (a) DML codes to create:
Book:
CREATE TABLE `book` (
`id` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
`author` varchar(150) NOT NULL,
`publisher` varchar(150) NOT NULL,
`pubYear` year(4) NOT NULL,
`isbnNo` int(11) NOT NULL,
PRIMARY KEY (id))
Staff:
CREATE TABLE `staff` (
`id` int(11) NOT NULL,
`sName` varchar(100) NOT NULL,
`address` varchar(255) NOT NULL,
`state` varchar(100) NOT NULL,
`contactNo` bigint(20) NOT NULL,

9DATABASE MANAGEMENT SYSTEM
`salary` int(11) NOT NULL,
`joinedDate` varchar(50) NOT NULL,
`dept` varchar(10) NOT NULL,
`position` varchar(30) NOT NULL,
`sex` enum('M','F') NOT NULL,
PRIMARY KEY (id))
Student:
CREATE TABLE `student` (
`id` varchar(10) NOT NULL,
`sName` varchar(150) NOT NULL,
`Address` varchar(255) NOT NULL,
`state` varchar(50) NOT NULL,
`contactNo` varchar(15) NOT NULL,
`sex` enum('M','F') NOT NULL,
`email` varchar(255) NOT NULL,
PRIMARY KEY (id)
)

10DATABASE MANAGEMENT SYSTEM
Borrow:
CREATE TABLE `borrow` (
`studentID` int(11) NOT NULL,
`bookID` int(11) NOT NULL
)
(b) DML codes to insert:
Staff:
INSERT INTO `staff` (`id`, `sName`, `address`, `state`, `contactNo`, `salary`, `joinedDate`, `dept`,
`position`, `sex`) VALUES
('A102585', 'Tan Chee Chong', '288, Lorong Duta, Pupa Village', 'Selangor', 192587461, 2500, '15-
Jan-06', 'SE', 'Lecturer', 'M');
Book:
INSERT INTO `book` (`id`, `title`, `author`, `publisher`, `pubYear`, `isbnNo`) VALUES
(00001, 'An introduction to Database Systems', 'A.K. Allen', 'McGrawHill ', 2001, 74895841);
Borrow:
INSERT INTO `borrow` (`studentID`, `bookID`) VALUES ('10010', 0025);
Student:
INSERT INTO `student` (`id`, `sName`, `Address`, `state`, `contactNo`, `sex`, `email`) VALUES

11DATABASE MANAGEMENT SYSTEM
('S10010', 'Chan Wai Yee', '56A, Jalan 2/14,Taman Midah', 'Kuala Lumpur', '012-3256780', 'F',
'[e-mail]');
(c) Enhancement features of SQL integrity:
(i) alter table borrow add constraint index_book_id foreign key (bookID) references book(id)
ALTER TABLE borrow ADD CONSTRAINT unique_book UNIQUE (bookID);
(ii)ALTER TABLE staffADD CHECK (salary>0);
4. Relational Algebra Operation
(a) i. כ COUNT id (σ salary > 2000 (Staff))
ii. π id, SName, contactNo(Student)
iii. π book.title, book.publisher(Book) book.id = book.id(Borrow)