DATABASE MANAGEMENT SYSTEM o Designer – group of people who actually works on

designing part of database.

MIDTERMS REVIEWER o End Users – group contains the persons who actually
take advantage of database system, they can be just
viewers who pay attention to the logs or market
TOPIC I. DBMS OVERVIEW AND ARCHITECTURE rates.
DBMS OVERVIEW

o Database – collection of data which is related by DBMS Architecture

some aspect.
o Data – collection of facts and figures which can be - it can be seen as single-tier or multi-tier. N-tier
processed to produce information. architecture divides the whole system into related
but independent n modules, which can be
Database Management System – it stores data in such a independently modified, altered, changed or replaced.
way which is easier to retrieve, manipulate and helps
produce information. o In 1-tier architecture, DBMS is the only entity
where user directly sits on DBMS and uses it.
o Real-World Entity – DBMS are more realistic and
o Programmers use 2-tier architecture where they
uses real-world entities to design its architecture.
access DBMS by means of application. Here
o Relation-based tables – DBMS allows entities and
application tier is entirely independent of
relations among them to form as tables.
database in term of operation, design and
o Isolation of Data and Application – DBS is entirely
programming.
different than its data. Data is an active entity; it is
o Most widely used architecture is 3-tier
also a passive one on which database works and
architecture. 3-tier architecture separates it tier
organizes.
from each other on basis of users.
o Less Redundancy – it follows rules of
o Database (Data) Tier – only database
normalization, which splits a relation when any of
resides. Also contains all relations and
its attributes is having redundancy in values.
their constraints.
o Consistency – it is a state where every relation in
o Application (Middle) Tier – it works
database remains consistent.
as mediator between the two. Users
o Query Language – it is equipped with query
are unaware of any existence of
language, which makes it more efficient to retrieve
database beyond application.
and manipulate data.
o User (Presentation) Tier – An end user
o ACID Properties – or Atomicity, Consistency,
sits on this tier. The user doesn’t know
Isolation, and Durability that maintain database in a
about the existence or form of database
healthy state in multi-transactional environment and
beyond this layer.
in case of failure.
o Multiuser and Concurrent Access – it supports DBMS Data Models
multi-user environment and allows them to access
and manipulate data in parallel.  Data model tells how the logical structure of a
o Multiple Views – this enables users to have a database is modeled.
concentrate view of database according to their  Data Models are fundamental entities to introduce
requirements. abstraction in DBMS.
o Security – users are unable to access data of other  Data models define how data is connected to each
users and departments. other and how it will be processed and stored inside
the system.

DBMS various purposes:

Entity-Relational Model – it is based on the notion of real-
o Administrators – are responsible for world entities and relationship among them. It is based on:
administrating the database, and to look after its
- Entities and their Attributes
usage and by whom it should be used.
- Relationships among Entities
 - An entity in ER Model is real world entity, which has o Simple Attribute – are atomic values
some properties called attributes. which cannot be divided further.
- The logical association among entities is called o Composite Attribute – are made of more
relationship. Relationships are mapped with entities than one simple attribute.
in various ways. Mapping cardinalities define the o Derived Attribute – the physical in the
number of associations between two entities: database system do not exist but their
o one to one values are derived from other attributes
o one to many presented in the database.
o many to one o Single-valued Attribute – it contains
o many to many single value.
o Multi-value Attribute – may contain more
than one values.
o Relational Model - is the most popular data model
These attribute types can come together in a way like:
in DBMS. It is more scientific model because it is
based on first-order predicate logic and defines tables  Simple single-valued attributes
as an n-ary relation.
 Simple multi-valued attributes

 Composite single-valued attributes

DBMS DATA SCHEMAS
 Composite multi-valued attributes
o Database Schema – a skeleton of database
structure and it represents the logical view of entire
database. Key – is an attribute or collection of attributes that
o Physical Database Schema – pertains to the uniquely identifies an entity among entity set.
actual storage of data and its form of
storage like files, indices, etc. o Super Key – set of attributes (one or more) that
o Logical Database Schema – all logical collectively identifies an entity in an entity set.
constraints that need to be applied on o Candidate Key – minimal super key for which no
data stored. Defines tables, views, and proper subset is a super key. An entity set may have
integrity constraints. more than one candidate key.
o Primary Key – one of the candidate keys chosen by
the database designer to uniquely identify the entity
set.
DBMS DATA INDEPENDENCE

o Logical Data Independence – kind of mechanism

which liberalizes itself from actual data stored on The association among entities is called relationship.
the disk.
o Physical Data Independence – is the power to  Binary = degree 2
change the physical data without impacting the  Ternary = degree 3
schema or logical data.
 n-ary = degree

TOPIC II. ENTITY RELATIONSHIP MODEL

o Cardinality - defines the number of entities in one
ER MODEL: Basic Concepts entity set which can be associated to the number of
o Entity Relationship Model – defines the conceptual entities of other set via relationship set.
view of database. it works around real-world entity o One-to-one – one entity can be associated
among them. with one entity or vice versa.
o Entity – A real-world thing either animate or o One-to-many – one entity can be associated
inanimate that can be easily identifiable and with more than one entity.
distinguishable. o Many-to-one – more than one entity can be
o Attribute – entities are represented by means of associated with one entity.
their properties.
 o Many-to-Many – many entities can be data definition, data manipulation and transaction
associated with many entities. management operations.
o Rule 6: View updating rule – This rule states that all
views of database, which can theoretically be
updated, must also be updatable by the system.
o Rule 7: High-level insert, update and delete rule –
ER DIAGRAM REPRESENTATION This rule states the database must employ support
o Entities – are represented by means of rectangles. high-level insertion, update and deletion.
o Rule 8: Physical Data Independence - states that
o Attributes – are properties of entities, and it is
the application should not have any concern about
represented by means of eclipses.
how the data is physically stored.
o Relationships – are represented by diamond box.
o Rule 9: Logical Data Independence - Any change
in logical data must not imply any change in the
application using it.
Participation Constraints o Rule 10: Integrity Independence - states that the
o Total Participation – each entity is involved in the database must be independent of the application
relationship. It is represented by double lines. using it.
o Partial Participation – not all entities are involved o Rule 11: Distribution Independence - states that the
in the relationship. It is represented by single line. end user must not be able to see that the data is
distributed over various locations.
o Rule 12: Non-Subversion Rule - states that if a
system has an interface that provides access to low
o Generalization – The process of generalizing
level records, this interface then must not be able to
entities, where the generalized entities contain the
subvert the system and bypass security and integrity
properties of all the generalized entities.
constraints.
o Specialization – a group of entities is divided into
sub-groups based on their characteristics.
o Inheritance – the attributes of higher-level entities
are inherited by the lower. Relational Data Model – it is the primary data model which
is used widely around the world.

o Relations are saved in the format of Tables.

TOPIC III. RELATIONAL MODEL o A singe row of a table, which contains a single
record for that relation is called a tuple.
Dr. Edgar F. Codd is the one who did some research in
o A finite set of tuples in the relational database
Relational Model of Database Systems.
system represents relation instance.
Codd’s 12 Rules o Relation Instances - do not have duplicate tuples.
o Relation schema - This describes the relation’s name
o Rule 1: Information Rule – states that all
(table name), attributes and their names.
information (data) must be a value of some table
o Relation key – each row has one or more
cell. Must be stored in table formats.
attributes which can identify the row in the relation
o Rule 2: Guaranteed Access Rule – states that every
(table) uniquely.
single data element is guaranteed to be accessible
logically with of table-name, primary-key (row Every attribute has some pre-defined value scope,
value) and attribute-name (column value). known as Attribute Domain. Every relation has some
o Rule 3: Systematic Treatment of NULL Values - conditions that must hold for it to be a valid relation. These
rule states the NULL values in the database must conditions are called Relational Integrity Constraints:
be given a systematic treatment.
o Rule 4: Active online catalog – states that the o Key Constraints – at least one minimal subset of
structure description of whole database must be attributes in the relation which can identify a tuple
stored in an online catalog. uniquely.
o Rule 5: Comprehensive Data Sub-Language Rule o No two tuples can have identical value.
– states that a database must have a support for a o Cannot have NULL VALUES.
language which has linear syntax which is capable of o Domain Constraints – attributes have specific
values in real-world scenario.
 o Referential Integrity Constraints - states that if a  Third Normal Form – it must be in Second
relation refers to a key attribute of a different or Normal Form.
same relation, that key element must exists. o Boyce-Codd Normal Form: BCNF is an
extension of Third Normal Form in strict
way.

ER MODEL TO RELATIONAL MODEL

DBMS Joins
ER Diagrams mainly comprised of:
 Join – combination of Cartesian Product followed
 Entity and its attributes by selection process.
 Theta (θ) join – combines tuples from different
 Relationship, which is association among entities. relations.
o Mapping Entity – entity is a real-world with some o Equi join – when Theta Join uses only
attributes. equality comparison operator.
o Mapping Relationship – a relationship is  Natural Join (⋈) – does not use any comparison
association among entities. operator.
o Mapping Weak Entity Sets – a weak entity sets are  Inner Joins – these are the Theta Join, Equi Join, and
one which does not have any primary key Natural join. It includes only tuples with matching
associated with it. attributes.
o Mapping Hierarchical Entities – ER specialization  Left Outer Join – all tuples of Left Relation, R, are
or generalization comes in the form of hierarchical included in the resulting relation.
entity sets.  Right Outer Join – all tuples of the Right Relation,
S, are included in the resulting relation.
Key Terms:  Full Outer Join - All tuples of both participating
relations are included in the resulting relation.
o Table – has rows and columns, where rows
represent records and columns represents the
attributes.
o Tuple – single row of a table, which contains a TOPIC V: INTRODUCTION TO MS ACCESS
single record for that relation. MS Access Overview
o Key – one or more attributes which can identify the
row in the relation (table) uniquely. o It stores data in its own format based on the Access
Jet Database Engine.
o Like relational databases, Microsoft Access also
TOPIC IV: RELATIONAL DATABASE DESIGN allows you to link related information easily.
o It can also import or link directly to data stored in
 Normalization – remove all anomalies. other applications and databases.
o Update Anomalies – if data items are o Microsoft Access stores information which is called a
scattered and are not linked to each other database. To use MS
properly.
o Deletion Anomalies – delete a record. But Access, you will need to follow these four steps:
parts of it left undeleted. o Database Creation - Create your Microsoft Access
o Insert Anomalies – insert data in a record database and specify what kind of data you will be
that does not exist at all. storing.
 First Normal Form – defines that all the attributes o Data Input - After your database is created, the data
in a relation must have atomic domains. of every business day can be entered into the Access
 Second Normal Form – says that every non-prime database.
attribute should be fully functionally dependent o Query - This is a fancy term to basically describe the
on prime-key attribute. process of retrieving information from the database.
o Prime Attribute – part of prime-key o Report (optional) - Information from the database is
attribute. organized in a nice presentation that can be printed in
o Non-Prime Attribute – not part of prime- an Access Report.
key attribute.
MS Access RDBMS Microsoft Access provides 4 different types of Action
Queries:
o Before MS Access 2007, the file extension was
*.mdb, but in MS Access 2007 the extension has been o Append
changed to *.accdb extension. o Update
o Delete
o Make-table

Query Criteria – helps you to retrieve specific items from

MS Access Objects an Access Database.

o Table - is an object that is used to define and store o Query Criterion – is an expression that access
data. compares to query field values.
o Query – An object that provides a custom view of o An action query cannot be undone.
data from one or more tables.
o Form - is an object in a desktop database designed Create Queries
primarily for data input or display or for control of o Update Query – to change data in your tables.
application execution. o Delete Query – to delete data from your tables.
o Report - is an object in desktop databases designed
for formatting, calculating, printing, and Parameter Queries
summarizing selected data.
o You can save and run the same query again and
o Access offers you the ability to create a
again but by only changing the criteria.
report from any table or query.
o Macro - a structured definition of one or more
actions that you want Access to perform in response
to a defined event.
o Module - is an object in desktop databases.

TOPIC VI: DATABASE & TABLE CREATION, DATA

TYPES & DATA ADDING

1. Create Blank Database

o Data Types – determines what kind of data it
can store.
2. Create Tables – you store your data in tables.
3. Adding Data – to view, change, insert, or delete
data in a table within Access.
o Datasheet – is a simple way to look at your
data in rows and columns without any special
formatting.
o Tables – you should always start your design of
a database by creating this and then creating any
other object.

TOPIC VII: QUERIES

Query Data – it is a request for data results, and for action

on data.

o Select Queries – used to retrieve data from a table

or to make calculations.
o Action Queries – queries that add, change, or
delete multiple records at one time.