FILE BASED SYSTEMS - Collection of application programs that perform services for the end users

MANUAL FILING SYSTEMS - while number of items to be stored is small for only storage or retrieval
functionality of large number of items

LIMITATIONS OF FILE-BASED APPROACH

- Separation and Isolation of Data - Users of one program may be unaware of potentially useful
data held by other programs

- Duplication of Data - Same data is held by different programs

- Data Dependence - File structure is defined in the program code

- Incompatible file formats - Programs are written in different languages and so cannot easily
access each other’s files

- Fixed Queries /Proliferation of application programs - Any new requirement needs a new
program

DATABASE AND DATABASE MANAGEMENT SYSTEM - Database is a shared collection of logically related
data and a description of data

 DATA DEFINITION LANGUAGE (DDL) - Permits specification of data types, structures and any
data constraints.

 DATA MANIPULATION LANGUAGE (DML) - General enquiry facility (query language) of the data

 CONTROLLED ACCESS TO DATABASE MAY INCLUDE

 A security system

• Which prevents unauthorized users accessing the database

 An integrity system

• Which maintains the consistency of stored data

 A concurrency control system

• Which allows shared access of the database

 A recovery control system

• Which restores the database to a previous consistent state in case of hardware

or software failure
  A user-accessible catalog

• Which contains description of the data in the database

VIEWS - Allows each user to have his or her own view of database

COMPONENTS OF DBMS ENVIRONMENT

 HARDWARE – can range from a PC to a network of computers.

• SOFTWARE – DBMS, operating system, network software (if necessary) and also the application
programs.

• DATA – used by the organization and a description of this data called schema.

• PROCEDURES – instructions and rules that should be applied to the design and use of the
database and DBMS.

• PEOPLE

ROLES IN THE DATABASE ENVIRONMENT

• Data Administrator (DA) - Database planning

• Database Administrator (DBA) - Physical realization of the database

• Database Designers – Logical / Physical

• Application Programmer

• End Users – Naïve / sophisticated

DBTG specified three languages:

- A schema Data Definition Language (DDL), which enables the DBA to define the schema.
 - A subschema DDL, which allows the application programs to define the parts of the database
they require.
- A Data Manipulation Language (DML) to manipulate the data.

 FIRST GENERATION - hierarchical and network

 SECOND GENERATION – relational
 THIRD GENERATION - object-relational / object-oriented

Ansi-Sparc Database Architecture: Three Levels

 Physical Level – Lowest level, known as internal level. Describe how data is stored in database.
 Conceptual Level - Higher level than Physical, known as logical level. Describe how database
appears to the user conceptually and relationships between various data tables.
 External Level – Highest level, known as the view level.

Database Languages in DBMS (Database Management System)

 Data Definition Language (DDS) – Use to create/store scheme, tables, indexes, constraint, etc.
in database.
 Commands
- Create – Create objects
- Alter – Alter structure
- Drop – Delete objects
- Truncate – Remove all records
- Rename – Rename an object
- Comment – Comment on data dictionary

 Data Manipulation Language (DML) – Accessing and manipulating data in database

 Commands
- Select – Retrieve data
- Insert – Insert data
- Update – Update existing data from the table
- Delete – Delete all records from a table
- Merge – Perform “UPSERT” (Insert or Update) operations
- Call – Call a structure query language or a java subprogram
- Explain Plan – Parameter of explaining data
- Lock Table – Controls concurrency

 Data Control Language (DCL) – Retrieved the stored or saved data

 Commands
- Grant – Give user access privileges
- Revoke – Used to take back permission from the user

a. Operations which have the authorization of Revoke:

 Connect
 Insert
 Usage
 Execute
 Delete
 Update
 Select

 Transaction Control Unit (TCL) – Run changes made by DML statement

 Commands
- Commit – Used to save the transaction
- Rollback – Used to restore the database to its original since last Commit
Data Models and Conceptual Modeling

 ER Model – Contains an extensive set of modeling tools / also popular design.

 Object Definition Language (ODL)

Database Design

 Conceptual Database Design – Information gathered in the requirements analysis step is used
to develop a high level description of the data to be stored in the database, along with the
constraints that are known to hold this data.

 Logical Database Design – Convert database design into a database schema within the data
model of the chosen DBMS

 Schema Refinement – Convert database design into a database schema within the data model
of the chosen DBMS

Data Modeling – Data modeling is the first step in designing a database, refers to the process of creating
a specific data model for a determined problem domain.

Data Models – A data model is a relatively representation, usually graphical, of more complex real-world
data structures. In general terms, a model is an abstraction of a more complex real-world object or
event.

The Importance of Data Models

- Data models are a communication tool.

1. Entities – A person, place, thing, concept, or event about which data will be collected and
stored.
2. Attribute – A characteristic of an entity.
3. Relationship – Describes an association among entities.
 One-to-many (1:M or 1..*)
 Many-to-many (M:N or *..*)
 One-to-one (1:1 or 1..1)
4. Constraint – A restriction placed on the data

Business Rules - a brief, precise, and unambiguous (clear) description of a policy, procedure, or principle
within a specific organization.
The Entity Relationship Model

 Entity Relationship Model (ERM)

 Forms the basis of an ERD

 Entity Relationship Diagram (ERD)

 Represents the conceptual database as viewed by the end user.

The Entity Relationship Model – Entities

 ER modeling level, an entity actually refers to the entity set and not to a single entity
occurrence, In other words, an entity in the ERM corresponds to a table—not to a row—
in the relational environment

The Entity Relationship Model – Attributes

 Attributes are characteristics of entities. For example, the STUDENT entity includes the
attributes STU_LNAME, STU_FNAME, and STU_INITIAL