fundamentals-of-database.pptx hehehehehe

• ENTITY
• ATTRIBUTE
• RELATIONSHIP
• BUSINESS RULE
terms to
remember

• Identify primary key, foreign key, and attributes.
• Create ERD
• Construct Business Rule
• Know how to use MySql
end of the lesson you
should...

What is entity?
In a DBMS, an entity is anything about which data
can be stored. Examples include a student, an
employee, or a bank account.

examples:
A product
A single Person
An Employee
A manager

What is attribute?
Something that describes or qualifies an entity. For
example, in the employee entity, the attributes would
be the employee number, name, job title, hire date,
department number, and so on. Each of the attributes is
either required
or optional. This state is called optionality.

examples:
ID
NAME
ADDRESS
PHONE NUMBER

2 TYPES OF
ATTRIBUTES
In a Database Management System (DBMS), an attribute is a
property or characteristic of an entity that is used to describe it.
Attributes can be simple or composite. A composite attribute is
an attribute that can be divided into smaller sub-parts, each
representing a more basic attribute with independent meaning

DECOMPOSITION/
NORMALIZATION
For instance, an address can be considered a composite
attribute because it can be divided into smaller parts such as
house number, street, city, state, and zip code. Similarly, a name
can be split into first name, middle name, and last name

EXAMPLE
A manager was given his manager id. He is also required to
give his name, address, date of birth and phone number for it is
kept by the owner.

What is
Relationship?
A named association between entities showing
optionality and degree. For example, an employee
assigned to a department is a relationship
between the employee and department entities.

Each direction of the
relationship contains:
A label: For example, taught by or assigned to
An optionality: Either must be or maybe
 A degree: Either one and only one or one or
more

• An employee is assigned to a department.
• At some time, the employees are in the shop but have not
been given duty yet.
• Each employee must belong to one and only one
department, but each department can have multiple
example

PRIMARY KEY
A primary key is used to ensure that data in the specific
column is unique. A column cannot have NULL values. It
is either an existing table column or a column that is
specifically generated by the database according to a
defined sequence.

FOREIGN KEY
A foreign key creates a relationship between two tables by
linking the foreign key in the child table to the primary
key in the parent table. This ensures that the value in the
foreign key field matches a value in the primary key field
of the parent table.

Many-to-many relationships are disallowed in a rela
tional database because of the problems they creat
e
.
These problems include data redundancy and data i
nsertion, deletion, and updating difficulties
.
If left as it is, many-to-many relationships can lead t
o data duplication, which is inefficient
WHy does m:M is not
allowed?

1. Identify participating tables
Start by identifying the tables involved in the many-to-many relationship. This initial st
ep is crucial for accurately defining and connecting the relationship in your ER diagra
m.
2. Create a new relationship entity
Introduce a new entity in your ER diagram to represent the many-to-many relationshi
p. Choose either a bridge/joining entity or an associative entity to structure the relatio
nship clearly.
3. Define relationship attributes
Define attributes within the new entity to capture essential relationship-specific detail
s. These attributes provide insights into how the tables interact, enhancing your ER di
agram's clarity.
how to represent
m:m ?

4. Establish entity relationships
Connect the new relationship entity with the participating tables. This step ensures y
our ER diagram accurately reflects how data flows between entities, aiding understan
ding and communication.
5. Specify cardinality and constraints
Specify the cardinality (such as one-to-many or many-to-many) and participation cons
traints of the relationships. This clarifies data interaction patterns, optimizing databa
se design and performance.
6. Add additional attributes (optional)
Enhance your ER diagram by including extra attributes that provide context or detaile
d information related to the many-to-many relationship. Tailoring the diagram to spe
cific needs improves its relevance and function in database design.
how to represent
m:m ?

example
➢ A duty is allocated to many employees, and
many duties can be allocated to a certain
employee. There are different projects given to
them for the employees to be able to perform
their duty, given their project quota daily.

Data types
• Numeric data types like INT, FLOAT, and DECIMAL are used for
storing different kinds of numerical values.
• String data types like CHAR and VARCHAR are used for storing
textual data with varying lengths.
• Date and time data types like DATE, TIME, and TIMESTAMP are used
for storing temporal data.

string
• CHAR: Stores fixed-length strings of characters. For example, if
you declare a CHAR column with a length of 5, it will always
occupy 5 characters' worth of storage. The default length is 1,
and the maximum length is 65,000 octets (bytes).
• VARCHAR: Stores variable-length strings. The default length is 80,
and the maximum length is 65,000 octets. When adding a VARCHAR
variable, you specify the maximum number of characters allowed.

Time and date
• DATE: Stores the year, month, and day in the formatYYYY-MM-DD.The supported
range is typically from 0000-01-01 to 9999-12-31.
• TIME: Stores the time in the format HH:MM:SS.
• DATETIME: Combines the DATE and TIME formats, storing both date and time
components.
• TIMESTAMP:An extension of the DATE type, storing the year, month, day, hour,
minute, and second. It can also store fractional seconds.

numeric
•INT (Integer): Stores whole numbers without decimal points, making it
ideal for IDs, counters, or data that doesn’t require fractions.
•FLOAT (Floating Point): Stores approximate fractional values with a large
range but less precision, suitable for scientific data or calculations where
exact precision isn’t critical.
•DECIMAL (or NUMERIC): Stores exact fractional values with user-defined
precision and scale, making it perfect for financial or monetary data
requiring exact calculations.

A relational database is a type of database that organizes data
into rows and columns, forming tables where data points are
related to each other. This structure allows for efficient data
management and retrieval. Each table in a relational database
has a unique identifier called a primary key, and tables can be
linked using foreign keys
WHAT IS RELATIONAL
DATABASE?

• Candidate Key
• Primary Key
• Super Key
• Alternate Key
• Foreign Key
• Composite Key
DIFFERENT TYPES OF
KEYS

WHY DO WE REQUIRE KEYS?
We require keys in a DBMS to ensure that data is organized,
accurate, and easily accessible. Keys help to uniquely identify
records in a table, which prevents duplication and ensures data
integrity.
Keys also establish relationships between different tables,
allowing for efficient querying and management of data.
Without keys, it would be difficult to retrieve or update specific
records, and the database could become inconsistent or

super key
The set of one or more attributes (columns) that can uniquely
identify a tuple (record) is known as Super Key. For Example,
STUD_NO, (STUD_NO, STUD_NAME), etc.
• A super key is a group of single or multiple keys that uniquely identifies
rows in a table. It supports NULL values in rows.
• A super key can contain extra attributes that aren’t necessary for
uniqueness. For example, if the “STUD_NO” column can uniquely identify a
student, adding “SNAME” to it will still form a valid super key, though it’s
unnecessary.

candidate key
The minimal set of attributes that can uniquely identify a
tuple is known as a candidate key. For Example, STUD_NO in
STUDENT relation.
• A candidate key is a minimal super key, meaning it can uniquely identify a record but contains no
extra attributes.
• It is a super key with no repeated data is called a candidate key.
• The minimal set of attributes that can uniquely identify a record.
• A candidate key must contain unique values, ensuring that no two rows have the same value in t
candidate key’s columns.
• Every table must have at least a single candidate key.
• A table can have multiple candidate keys but only one primary key.

alternate key
An alternate key is any candidate key in a table that is not
chosen as the primary key. In other words, all the keys that
are not selected as the primary key are considered alternate
keys.• An alternate key is also referred to as a secondary key because it can
uniquely identify records in a table, just like the primary key.
• An alternate key can consist of one or more columns (fields) that can
uniquely identify a record, but it is not the primary key
• Eg:- SNAME, and ADDRESS is Alternate keys

composite key
Sometimes, a table might not have a single column/attribute that uniquely
identifies all the records of a table. To uniquely identify rows of a table, a
combination of two or more columns/attributes can be used. It still can
give duplicate values in rare cases. So, we need to find the optimal set of
attributes that can uniquely identify rows in a table.
• It acts as a primary key if there is no primary key in a table
• Two or more attributes are used together to make a composite key .
• Different combinations of attributes may give different accuracy in
terms of
identifying the rows uniquely.

fundamentals-of-database.pptx hehehehehe

More Related Content

Similar to fundamentals-of-database.pptx hehehehehe

Recently uploaded

fundamentals-of-database.pptx hehehehehe