DBMS MOD 3 REVIEWER the student_number of a student makes him/her)

identifiable among students.

TYPES OF KEYS:
ER MODEL - defines the conceptual view of a
database. It works around real-world entities and Super Key – A super key is a group of single or
the associations among them. At view level, the ER multiple keys which identifies rows in a table.
model is considered a good option for designing
Primary Key – is a column or group of columns in
databases.
a table that uniquely identify every row in that table.
ENTITY
Candidate Key – is a set of attributes that uniquely
- are represented by means of their properties identify tuples in a table. Is a super key with no
called attributes. repeated attributes.
- entity can be a real-world object, either animate or Alternate Key – is a column or group of columns in
inanimate, that can be easily identifiable. (EX: a table that uniquely identify every row in that table.
students, teachers)
Foreign Key – is a column that creates a
- entity set is a collection of similar types of entities. relationship between two tables. Purpose of
An entity set may contain entities with attribute Foreign keys is to maintain data integrity and allow
sharing similar values. navigation between two different instances of an
entity.
ATTRIBUTES
Compound Key – has two or more attributes that
- represented by means of their properties
allow you to uniquely recognize a specific record. It
- All attributes have values. For example, a student is possible that each column may not be unique by
entity may have name, class, and age as attribute itself within the database.

- There exists a domain or range of values that can Composite Key – is a combination of two or more
be assigned to attributes. For example, a student's columns that uniquely identify rows in a table. The
name cannot be a numeric value. It has to be combination of columns guarantees uniqueness,
alphabetic. A student's age cannot be negative, etc. though individual uniqueness is not guranteed.

TYPES OF ATTRIBUTES: Surrogate Key – An artificial key which aims to

uniquely identify each record is called a surrogate
• Simple attribute - are atomic values, which key. These kind of key are unique because they are
cannot be divided further. For example, a student's created when you don’t have any natural primary
phone number is an atomic value of 10 digits. key.

• Composite attribute - are made of more than RELATIONSHIP - The association among entities.
one simple attribute. For example, a student's (EX: an employee works_at a department, a
complete name may have first_name and student enrolls in a course. Here, works_at and
last_name. enrolls are called relationships.)
RELATIONSHIP SET - A set of relationships of
• Derived attribute - are the attributes that do not similar type.
exist in the physical database, but their values are
derived from other attributes present in the DISTRIBUTIVE ATTRIBUTES - Like entities, a
database. For example, age can be derived from relationship too can have attributes.
date_of_birth.
DEGREE OF RELATIONSHIP:
• Single-value attribute - contain single value. For The number of participating entities in a relationship
example: Social_Security_Number. defines the degree of the relationship.
• Unary = degree 1
• Multi-value attribute - may contain more than • Binary = degree 2
one values. For example, a person can have more • Ternary = degree 3
than one phone_number, email_address, etc. • n-ary = degree
These attribute types can come together in a BINARY RELATIONSHIP - A relationship where
way like: two entities are participating.
• simple single-valued attributes
• simple multi-valued attributes CARDINALITY - is the number of instance of an
• composite single-valued attributes entity from a relation that can be associated with
• composite multi-valued attributes the relation.

Key - is an attribute or collection of attributes that

uniquely identifies an entity among entity set. (EX:
MAPPING CARDINALITIES Many-to-one − when more than one instance of
entity is associated with the relationship, it is
One-to-one − One entity from entity set A can be
marked as 'N:1'. The following image reflects that
associated with at most one entity of entity set B
more than one instance of an entity on the left and
and vice versa.
only one instance of an entity on the right can be
associated with the relationship. It depicts many-to-
one relationship.

Many-to-many − The following image reflects that

One-to-many − One entity from entity set A can be more than one instance of an entity on the left and
associated with more than one entities of entity set more than one instance of an entity on the right can
B however an entity from entity set B, can be be associated with the relationship. It depicts many-
associated with at most one entity. to-many relationship.

PARTICIPATION CONSTRAINTS
• Total Participation − each entity is involved in the
Many-to-one − More than one entities from entity relationship. Total participation is represented by
set A can be associated with at most one entity of double lines.
entity set B, however an entity from entity set B can
be associated with more than one entity from entity • Partial participation − not all entities are involved
set A. in the relationship. Partial participation is
represented by single lines.

ER DIAGRAM REPRESENTATION
Many-to-many − One entity from A can be
associated with more than one entity from B and Entity - are represented by means of rectangles.
vice versa. Rectangles are named with the entity set they
represent.

Relationship - which are represented by diamond

shapes, show how two entities share information in
the database.
Attributes - are the properties of entities. Attributes
are represented by means of ellipses. Every ellipse
One-to-one − when only one instance of an entity represents one attribute and is directly connected
is associated with the relationship, it is marked as to its entity (rectangle).
'1:1'. The following image reflects that only one
instance of each entity should be associated with
the relationship. It depicts one-to-one relationship.

Multivalued attributes are depicted by double

One-to-many − when more than one instance of an ellipse.
entity is associated with a relationship, it is marked
as '1:N'. The following image reflects that only one
instance of entity on the left and more than one
instance of an entity on the right can be associated
with the relationship. It depicts one-to-many
relationship.
Derived attributes are depicted by dashed ellipse.

GENERALIZATION - process of generalizing

entities, where the generalized entities contain the
properties of all the generalized entities.
- a number of entities are brought together into one
generalized entity based on their similar
characteristics

SPECIALIZATION - is the opposite of

generalization. In specialization, a group of entities
is divided into sub-groups based on their
characteristics.

ABSTRACTION - The details of entities are

generally hidden from the user.
INHERITANCE (feature of generalization) - It
allows lower-level entities to inherit the attributes of
higher-level entities.
SQL DDL, DML, DCL and TCL in SQL
• SQL stands for Structured Query Language.
DDL (Data Definition Language)
• SQL is a standard language for accessing and
manipulating databases. • DDL allows you to create SQL statements to
• SQL became a standard of the American make operations with database data structures
National Standards Institute (ANSI) in 1986, and (schemas, tables etc.).
of the International Organization for
Standardization (ISO) in 1987. DML (Data Manipulation Language)

• DML is used to build SQL queries to manipulate

Example of a Database (select, insert, update, delete etc.) data in the
database.

DCL (Data Control Language)

• Its commands are responsible for access

restrictions inside of the database.

TCL (Transaction Control Language)

What SQL can do?
• Its commands are used to manage transactions
• SQL can execute queries against a database
in SQL databases.
• SQL can retrieve data from a database

• SQL can insert records in a database

SQL Commands List
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures,
and views

Using SQL in Web Sites

To build a web site that shows data from a database,

you will need:

• An RDBMS database program (i.e. MS Access,

SQL Server, MySQL)
DDL Commands
• To use a server-side scripting language, like PHP
CREATE
or ASP
CREATE statement is used to create a new database,
• To use SQL to get the data you want
table, index or stored procedure.
• To use HTML / CSS to style the page
 DML Commands

DROP SELECT

DROP statement allows you to remove database, SELECT query is used to retrieve a data from SQL
table, index or stored procedure. tables.

INSERT

INSERT command is used to add new rows into the

ALTER database table.
ALTER is used to modify existing database data
structures (database, table).

UPDATE
RENAME
UPDATE statement modifies records into the table.
RENAME command is used to rename SQL table.

TRUNCATE DELETE
TRUNCATE operation is used to delete all table DELETE query removes entries from the table.
records.

Logically it’s the same as DELETE command.

Differences between DELETE and TRUNCATE

commands are:
SELECT / FROM / WHERE
• TRUNCATE is really faster
• SELECT − this is one of the fundamental query
• TRUNCATE cannot be rolled back command of SQL. It is similar to the projection
operation of relational algebra. It selects the
• TRUNCATE command does not invoke ON
attributes based on the condition described by
DELETE triggers
WHERE clause.

• FROM − this clause takes a relation name as an

argument from which attributes are to be
selected/projected. In case more than one
relation names are given, this clause
corresponds to Cartesian product.

• WHERE − this clause defines predicate or

conditions, which must match in order to
qualify the attributes to be projected.
 For example, I want to grant all privileges to
‘explainjava’ database for user ‘dmytro@localhost’.

This command will yield the names of authors from

the relation book_author whose age is greater than
50.

INSERT INTO / VALUES

This command is used for inserting values into the

rows of a table (relation).

REVOKE

REVOKE statement is used to remove privileges

UPDATE / SET / WHERE from user accounts.

This command is used for updating or modifying the

values of columns in a table (relation).

DELETE / FROM / WHERE

This command is used for removing one or more TCL Commands

rows from a table (relation).
START TRANSACTION (BEGIN, BEGIN WORK)

START TRANSACTION is used to start a new SQL

transaction.

BEGIN and BEGIN WORK are aliases for START

TRANSACTION.

DCL Commands

GRANT

GRANT command gives permissions to SQL user after that, you’re doing manipulations with a data
account. (insert, update, delete) and at the end, you need to
commit a transaction.
COMMIT • Access cookies variables and set cookies.

As a mentioned above COMMIT command finishes • Using PHP, you can restrict users to access some
transaction and stores all changes made inside of a pages of your website.
transaction.
• It can encrypt data.

Features of PHP

ROLLBACK

ROLLBACK statement reverts all changes made in

the scope of transaction.

PHP
Features of PHP
• PHP is an acronym for "PHP: Hypertext
Preprocessor" • Performance: PHP script is executed much
faster than those scripts which are written in
• PHP is an open-source, interpreted, and object- other languages such as JSP and ASP. PHP uses
oriented scripting language that can be its own memory, so the server workload and
executed at the server-side, which is used to loading time is automatically reduced, which
manage the dynamic content of the website. results in faster processing speed and better
performance.
• PHP supports many databases (MySQL,
Informix, Oracle, Sybase, Solid, PostgreSQL, • Open Source: PHP source code and software are
Generic ODBC, etc.) freely available on the web. You can develop all
the versions of PHP according to your
• PHP runs on different platforms (Windows,
requirement without paying any cost. All its
Linux, Unix, etc.)
components are free to download and use.
• PHP is compatible with almost all servers used
• Familiarity with syntax: PHP has easily
today (Apache, IIS, etc.)
understandable syntax. Programmers are
Common Uses of PHP comfortable coding with it.

• PHP performs system functions, i.e. from files • Embedded: PHP code can be easily embedded
on a system it can create, open, read, write, and within HTML tags and script.
close them.
• Platform Independent: PHP is available for
• PHP can handle forms, i.e. gather data from WINDOWS, MAC, LINUX & UNIX operating
files, save data to a file, through email you can system. A PHP application developed in one OS
send data, return data to the user. can be easily executed in other OS also.

• You add, delete, modify elements within your • Database Support: PHP supports all the leading
database through PHP. databases such as MySQL, SQLite, ODBC, etc.
• Error Reporting: PHP has predefined error MySQL
reporting constants to generate an error notice
• MySQL is a widely used relational database
or warning at runtime. E.g., E_ERROR,
management system (RDBMS).
E_WARNING, E_STRICT, E_PARSE.
• MySQL is free and open-source.
• Loosely Typed Language: PHP allows us to use a
variable without declaring its datatype. It will be • MySQL is ideal for both small and large
taken automatically at the time of execution applications.
based on the type of data it contains on its
value. • MySQL is very fast, reliable, scalable, and easy
to use.
• Web servers Support: PHP is compatible with
almost all local servers used today like Apache, • MySQL is cross-platform.
Netscape, Microsoft IIS, etc. • MySQL is compliant with the ANSI SQL standard.
• Security: PHP is a secure language to develop • MySQL was first released in 1995.
the website. It consists of multiple layers of
security to prevent threads and malicious • MySQL is developed, distributed, & supported
attacks. by Oracle Corporation.

• Control: Different programming languages • MySQL is named after co-founder Monty

require long script or code, whereas PHP can do Widenius's daughter: My.
the same work in a few lines of code. It has
maximum control over the websites like you can
make changes easily whenever you want. To build a web site that shows data from a database,
you will need:
• A Helpful PHP Community: It has a large
community of developers who regularly updates • An RDBMS database program (like MySQL)
documentation, tutorials, online help, and FAQs.
• A server-side scripting language, like PHP
Learning PHP from the communities is one of
the significant benefits. • To use SQL to get the data you want

PHP File • To use HTML / CSS to style the page

• PHP files can contain text, HTML, CSS,

JavaScript, and PHP code
Who Uses MySQL
• PHP code is executed on the server, and the
• Huge websites like Facebook, Twitter, Airbnb,
result is returned to the browser as plain HTML
Booking.com, Uber, GitHub, YouTube, etc.
• PHP files have extension ".php"
• Content Management Systems like WordPress,
PHP Project Output (Login Form) Drupal, Joomla!, Contao, etc.

• A very large number of web developers around

the world

What is RDBMS?

• RDBMS stands for Relational Database

Management System.

• RDBMS is a program used to maintain a

relational database.
• RDBMS is the basis for all modern database
systems such as MySQL, Microsoft SQL Server,
Oracle, and Microsoft Access.

• RDBMS uses SQL queries to access the data in

the database.