prelim lesson2 Flashcards
DBMS is the only entity where user directly sits
on DBMS and uses it. Any changes done here
will directly be done on DBMS itself. It does
not provide handy tools for end users and
preferably database designer and
programmers use single tier architecture.
1tier
- DBMS is 2-tier then must have some application,
which uses the DBMS
. Programmers use ____ architecture where they access DBMS by means of
application. Here application tier is entirely
independent of database in term of operation, design
and programming.
2tier
Most widely used architecture
3tier
tells how the logical structure of a database is modeled.
Data Models are fundamental entities to introduce abstraction in
DBMS. Data models define how data is connected to each other and
how it will be processed and stored inside the system.
Data model
- precise description of the data
content in a system. - Underlying structure of a
database. - Conceptual tool.
- Data + Relationship +
Semantics + Constraints - Design the database at physical,
logical and view level - The data modelling or data structuring
represents the nature of data, and the business
logic to control the data. It is also organize the
database. - The structure of data are explicitly determines
by the data model. - Data model helps to communicate between
business people, who requires the computer
system, and the technical people who can fulfill
their requirements.
Data modeling
Why do we create data models
- To aid in the development of a sound
database design that does not allow
anomalies or inconsistencies - Goal: to create database tables that do not
contain duplicate data values that can
become inconsistent
a class of real world objects having
common attributes (e.g., sites, variables,
methods).
Entity
A characteristic or property of an
entity (site name, latitude, longitude)
Attribute
an association between two or
more entities
relationship
the number of entities on either
end of a relationship (one-to-one, one-to many, many-to-many, etc.)
Cardinality
An entity in ER model is real world entity, which
has some properties called attributes. Every attribute is
defined by its set of values called domain.
Entity
The logical association among entities is
called relationship. Relationships are mapped with
entities in various ways. Mapping cardinalities define
the number of association between two entities.
Relationship
Relationship types
- One to one
- One to Many
- Many to one
- Many to Many
are represented by means of
rectangles. Rectangles are named with the
entity set they represent.
Entity
______ are the properties of entities.
_____ are represented by means of ellipses.
Every ellipse represents one attribute and is
directly connected to its entity (rectangle).
Attributes
If the attributes are _____, they are further
divided in a tree like structure. Every node is then
connected to its attribute. That is, composite
attributes are represented by ellipses that are
connected with an ellipse.
Composite Attributes
attributes are depicted by dashed
ellipse
Derived
attributes are depicted by double
ellipse.
Multivalued
T-A
____ attributes are atomic values,
which cannot be divided further. For example, a
student’s phone number is an atomic value of 10 digits.
Simple attribute
T-A
are made
of more than one simple attribute. For example, a
student’s complete name may have first_name and
last_name.
Composite Attribute
T-A
are the
attributes that do not exist in the physical database, but
their values are derived from other attributes present in
the database. For example, average_salary in a
department should not be saved directly in the
database, instead it can be derived. For another
example, age can be derived from data_of_birth.
Derived attribute
T-A
attributes contain
single value. For example − Social_Security_Number.
Single-value attribute
T-A
may
contain more than one values. For example, a person
can have more than one phone number, email_address,
etc.
Multi-value attribute
Entity set and keys
A set of attributes (one or
more) that collectively identifies an entity in
an entity set.
is a group of single or multiple
keys which identifies rows in a table. A Super
key may have additional attributes that are
not needed for unique identification.
Super key
Entity set and keys
A minimal super key is
called a candidate key. An entity set may
have more than one candidate key.
is a set of attributes that
uniquely identify tuples in a table. Candidate
Key is a super key with no repeated attributes.
The Primary key should be selected from the
candidate keys. Every table must have at least
a single candidate key. A table can have
multiple candidate keys but only a single
primary key.
Candidate key
Entity-set and keys
is one of the
candidate keys chosen by the database
designer to uniquely identify the entity set.
is a column or group of columns in a table that uniquely identify
every row in that table. The Primary Key can’t be a duplicate meaning the same
value can’t appear more than once in the table. A table cannot have more than
one primary key.
Primary
defines the number of entities in one
entity set, which can be associated with the
number of entities of other set via relationship set.
Cardinality
One entity from entity set A can be
associated with at most one entity of entity set B
and vice versa.
One-to-one
One entity from entity set A can
be associated with more than one entities of
entity set B however an entity from entity set B,
can be associated with at most one entity
One-to-many
More than one entities from
entity set A can be associated with at most one
entity of entity set B, however an entity from
entity set B can be associated with more than
one entity from entity set A
Many-to-one
One entity from A can be
associated with more than one entity from B
and vice versa.
Many-to-many
has the power of expressing
database entities in a conceptual hierarchical
manner. As the hierarchy goes up, it generalizes
the view of entities, and as we go deep in the
hierarchy, it gives us the detail of every entity
included
ER model
Two types of ER model
Generalization
Specialization
the process of generalizing entities, where the generalized
entities contain the properties of all the generalized entities, is
called generalization. In generalization, a number of entities
are brought together into one generalized entity based on
their similar characteristics. For example, pigeon, house
sparrow, crow and dove can all be generalized as Birds
Generalization
, a group of entities is
divided into sub-groups based on their
characteristics. Take a group ‘Person’ for
example. A person has name, date of
birth, gender, etc. These properties are
common in all persons, human beings.
But in a company, persons can be
identified as employee, employer,
customer, or vendor, based on what role
they play in the company.
Specialization
Inheritance is an important
feature of Generalization and
Specialization. It allows lowerlevel entities to inherit the
attributes of higher-level entities.
Inheritance
Every relation has some conditions that must hold for it to be a valid relation. These
conditions are called R____ I_____ C_____
Constraints
Three main integrity constraints
Key constraints
Domain constraints
Referential integrity constraints
There must be at least one minimal subset of
attributes in the relation, which can identify a tuple
uniquely. This minimal subset of attributes is
called key for that relation. If there are more than
one such minimal subsets, these are
called candidate keys.
* Key constraints force that −
* in a relation with a key attribute, no two
tuples can have identical values for key
attributes.
* a key attribute can not have NULL values.
Key constraints
is a key used to link two tables together
is a field (or collection of fields) in one table that
refers to the PRIMARY KEY in another table.
Foreign key
