By Ms Payal Gupta

Chapter 11-CS
Database and Data Modelling
File Based System
 Data stored in discrete files, stored on computer, and can be accessed, altered or removed

by the user

Disadvantages of File Based System:

 No enforcing control on organization/structure of files

 Data repeated in different files; manually change each

 Sorting must be done manually or must write a program

 Data may be in different format; difficult to find and use

 Impossible for it to be multi-user; chaotic

 Security not sophisticated; users can access everything

Database Management Systems (DBMS)

 DBMS: Software programs that allow databases to be defined, constructed and

manipulated

Features of a DBMS:

 Data management: data stored in relational databases - tables stored in secondary

storage

 Data dictionary contains:

o List of all files in database

o No. of records in each file

o Names & types of each field

 Data modeling: analysis of data objects used in database, identifying relationships

among them
 By Ms Payal Gupta

 Logical schema: overall view of entire database, includes: entities, attributes and

relationships

 Data integrity: entire block copied to user’s area when being changed, saved back

when done

 Data security: handles password allocation and verification, backups database

automatically, controls what certain user’s view by access rights of individuals or

groups of users

Data change clash solutions:

 Open entire database in exclusive mode – impractical with several users

 Lock all records in the table being modified – one user changing a table, others can

only read table

 Lock record currently being edited – as someone changes something, others can

only read record

 User specifies no locks – software warns user of simultaneous change, resolve

manually

 Deadlock: 2 locks at the same time, DBMS must recognize, 1 user must abort task

Tools in a DBMS:

 Developer interface: allows creating and manipulating database in SQL rather than

graphically

 Query processor: handles high-level queries. It parses, validates, optimizes, and

compiles or interprets a query which results in the query plan.

Relational Database Modelling

 Table: contains a group of related entities in rows and columns called an entity set

 Tuple: a row or a record in a relational database

 Attribute: a field or column in a relational database

 Primary key: attribute or combination of them that uniquely define each tuple in relation

 Candidate key: attribute that can potentially be a primary key

 Foreign key: attribute or combination of them that relates 2 different tables

 Referential integrity: prevents users or applications from entering inconsistent data

 Secondary key: candidate keys not chosen as the primary key

 Indexing: creating a secondary key on an attribute to provide fast access when searching

on that attribute; indexing data must be updated when table data changes

Relational Design of a System

Normalization
1st Normal Form (1NF): contains no repeating attribute or groups of attributes.

Intersection of each tuple and attribute contains only 1 value. Example:

2nd Normal Form (2NF): it is in 1NF and every non-primary key attribute is fully

dependent on the primary; all the incomplete dependencies have been removed.

Example:

3rd Normal Form (3NF): it is in 1NF and 2NF and all non-key elements are fully

dependent on the primary key. No inter-dependencies between attributes.

 MANY-TO-MANY functions cannot be directly normalized to 3NF, must use a 2 step process

e.g.

becomes:
 By Ms Payal Gupta

Data Definition Language (DDL)

o written in SQL

 Creating a database:

CREATE DATABASE <database-name>

 Creating a table:

CREATE TABLE <table-name> (…)

 Changing a table:

ALTER TABLE <table-name>

 Adding a primary key:

PRIMARY KEY (field)

ADD <field-name>:<data-type>

 Adding a foreign key:

FOREIGN KEY (field) REFERENCES <table>(field)

 Example:

CREATE DATABASE ‘Personnel.gdb’

CREATE TABLE Training

(EmpID INT NOT NULL,

CourseTitle VARCHAR(30) NOT NULL,

CourseDate Date NOT NULL,

PRIMARY KEY (EmpID, CourseDate),

FOREIGN KEY (EmpID) REFERENCES Employee(EmpID))

Data Manipulation Language (DML)

Queries:

 Creating a query:

 SELECT <field-name>

 FROM <table-name>

 WHERE <search-condition>
 By Ms Payal Gupta

 SQL Operators:

= Equals to

> Greater than

< Less than

>= Greater than or equal to

<= Less than or equal to

<> Not equal to

IS NULL Check for null values

 Sort into ascending order:

ORDER BY <field-name>

 Arrange identical data into groups:

GROUP BY <field-name>

 Joining together fields of different tables:

INNER JOIN

Data Maintenance:

 Adding data to table:

INSERT INTO <table-name>(field1, field2, field3)

VALUES (value1, value2, value3)

 Deleting a record:

DELETE FROM <table-name>

WHERE <condition>

 Updating a field in a table:

UPDATE <table-name>

SET <field-name> = <value>

WHERE <condition>