Intro to database systems

What is a database?

Database is a collection of related data and data is a collection of facts and figures that can be processed to produce information

How is database structured ?

Database is structured and organized collection of data that is stored, managed , and accessed using a specific software which is Database Management Systems (DBMS)

What is a DBMS?

It's to efficiently store and retrieve large amount of data while maintaining data integrity, security, and reliability. They are used to store various types of information , ranging from simple lists to complex structured data

What is Database Management system ?

It's a technology of storing and retrieving users data with utmost efficiency along with appropriate security measures

What does a DBMS provide?

A DBMS provides a interface for users and applications to interact with databases without needing to understand the underlying details of data storage and manipulation.

What are the following characteristics of DBMS?

A) real world entity

B) relation based tables

C) isolation of data and application

D) less redundancy

E) consistency

F) query language

G) All the above

G) All the above

Define real-world entity ?

Modern DBMS is more realistic and uses real world entities to design its architecture

Define relation-based tables?

DBMS allows entities and relations among them to form tables

Define isolation of data and application?

A database system is entirely different than its data

Define less redundancy?

Follows the rules of normalization, which splits a relation when any of its attributes is having redundancy in values

Define consistency?

Consistency is a state where every relation in a database remains consistent

Define query language?

DBMS is equipped with query language , which makes it more efficient to retrieve and manipulate data

What does ACID properties contain of?

follows the concept of atomicity, consistency , isolation , durability

What is Multiuser and Concurrent Access in DBMS?

With DBMS it supports multiuser environment and allows them to access and manipulate data in parallel.

What is multiple views and security in DBMS?

Multiple view is when DBMS offers multiple views for different users.

Security: is features like multiple views of offer security to some extent where users are unable to access data of other users and departments.

What is data?

Data are known facts that can be recorded and have an implicit meaning

Name the 3 DBMS users?

Administrators: they maintain the DBMS and are responsible for administrating database

Designers : are the group of people who actually work on designing part of the database

End Users: End users are those who actually reap the benefits of having DBMS

What is an Entity?

An entity can be a real world object either living or non-living like a person , object , place , concept , or event

What does ERD stand for?

It stands for Entity Relationship diagram

What's a data entity instance?

It's a specific occurrence (data value) of an entity and an entity must have multiple entity instances or it's not really an entity

Here is an example:

• if we have an employee table, then each employee in that table is an entity instance that is identified by the Emp_ID

What are Attributes?

An Attribute is a component which shows the properties or characteristics of an entity in the entity-relationship model.

In the entity-relationship diagram, attributes are represented by an oval or ellipse shape.

In each diagram, every oval shape represents an attribute which is directly connected to an entity.

What is ER Model Notation?

• Represent entities as rectangles

• List attributes within the rectangle

( similar as we did in lab 2)

Types of Attributes

• The different types of attribute in the ER model:

• Simple Attribute

• Derived Attribute

• Composite Attribute

• Multi-valves Attribute

• Key Attribute

Explain Simple attribute ?

Simple attributes are those attributes of an entity, which cannot be divided further into smaller components.

Following are the example of a simple attribute:

Roll no of the Student can be a simple attribute,

Id of the Employee can be a simple attribute,

Salary of the Employee can be a simple attribute,

Age and class of the student can be a simple attribute; etc

Explain Composite Attribute?

• Composite Attributes are those attributes of an entity, which is a combination of more than one attributes

• In the Entity-Relationship diagram, this attribute is represented by an ellipse comprising of other ellipses.

Address of an Employee is a composite attribute

The name of an employee is an composite attribute

Explain Multi-valves attributes?

• Multi-valued Attributes are those attributes of an entity, which contain more than one value.

Example:

Mobile number of the Employee is a multi-valued attribute,

Email ID and address of the employee also be a multi-valued attribute, etc.

Explain Derived attribute?

Derived Attributes are those attributes of an entity, which are based on other attributes and whose values are derived from other stored attributes.

> Age (Derived from Date of birth) of a student of an employee,

> Total or Average Marks of a student are derived attributes, etc.

• In an Entity-Relationship diagram, this attribute is represented by the dashed oval or ellipse shape.

What are the key attributes?

In the Entity-Relationship (ER) model, there are several types of keys used to uniquely identify entities and relationships. Here are the key types commonly used in ER modeling:

Super key : is a set of one or more attributes(columns) that can be used uniquely to identify entity within an entity set . Which may contain extra attributes which are not strictly necessary

Candidate key: is a minimal super key , meaning it's a subset of attributes within a super key that still uniquely identifies each entity . And a realtor may hav multiple candidate keys

Primary key: is a specific candidate key chosen to be the main method of uniquely identifying entities within a relation

Pt2 of remaining key attributes!

• Alternate Key: An alternate key is a candidate key that is not chosen as the primary key. It can be used as an alternative method for uniquely identifying entities if needed.

• Composite Key: A composite key is a primary key that consists of multiple attributes (columns) combined to form a unique identifier. It's used when no single attribute can uniquely identify an entity.

• Foreign Key: While not a key used to identify entities, a foreign key is used to establish relationships between entities. It is an attribute in one entity that refers to the primary key of another entity, creating a link between them.

• Surrogate Key: A surrogate key is an artificial, system-generated identifier used as the primary key when there is no suitable natural key. These keys have no inherent meaning but are solely used for database management purposes

Define Cardinality Constraints

are rules or limitations that define the relationships between tables in a database schema and maintains data integrity and data consistency .

one-to-one relationship (1:1)

when each record in one table relates to exactly one record in another. Used for tightly coupled, singular connections

ex: customer and spouse association

one-to-many relationship (1:M)

when each record in one table can relate to multiple records in another. its common for hierarchies and collections.

Example: Customers and their multiple orders

Many to Many (N:N) Relationship

Records in one table that can relate to multiple records in another , vice versa .

What does data modeling process do ?

We need to define the entities and attributes and relationships and the relationships cardinality

what are the 4 types of Cardinalities?

1) Mandatory One

2) Mandatory Many

3) Optional one

4) Optional Many

what is relationship lower bounds?

when having minimum number of related instances in a relationship

Explain what is a Composite Relationship?

it occurs for relationships that has attributes and these additional attributes provide more context or information about the relationship between the entities and it always has a Many to Many cardinality

What is generalization relationships?

it uses the bottom-up process and identifies common features of entity types and creates a high level entity (super class) using common features

what is Specialization relationship?

its a top-down process and starts from a single entity, creating low level entities (subclasses) using some different features

Whats the difference between Generalization and specialization relationship?

the difference between them is that generalization uses a down to top process and it starts to identify common features in entity types whilst specialization uses top down process and starts to create low-level entities

Define Homonyms

different attributes have the same name

Define Synonym

same attribute has different names in different tables

what is Database Schema?

is the skeleton structure that represents the logical view of the entire database it defines how the data is organized and how the relations among them are associated and formulates all the constraints that are to be applied on the data.

What are Key Fields?

keys are special fields that serve two main purposes which are primary key that are unique identifiers of the relation and foreign key which are identifiers that enable a dependent relation to refer to its parent relation

ER model to relational model

is after designing the ER diagram of system we need to convert it to a relational model which can be directly implemented by any DBMS

Tell me some key principles steps involving the transformation process?

First each entity in the ER diagram corresponds to a table in the relational schema .

2nd each attributes of an entity becomes a column in the corresponding table .

3rd the primary key ensures each row in the table is unique .

4th the relationships btwn entities ate represented using a foreign keys in the table

5th if an attribute has a multivalued ot composite values , additonal tables may be needed to represent these attribute properly

weak entity

is an entity thats dependent that becomes a separate relation with foreign key taken from the superior entity

What are the different types of Mapping a Binary Relationships?

1st is One-to-one primary key on the mandatory side becomes a foreign key on the optional side.

2nd is One-to-many primary key on the one side becomes a foreign key on the many side.

3rd is Many-to-Many create a relation with the primary keys of two entities as its primary key

Mapping Unary Relationships

One-to-Many : Recursive foreign key in the same relation

Many-to-Many has

Two relations:

One for the entity type

One for an associative relation in which the primary key has two attributes, both taken from the primary key of the entity

Define data normalization

the process of efficiently organizing data in a database and process of decomposing relations with anomalies to produce smaller , well-structured relations

What is a well-structured relation?

A relation that contains minimal redundancy and allows users to insert, modify, and delete the rows in a table without errors or inconsistencies.

What is anomalies?

data anomalies (irregularities). They threaten data integrity.

Define functional Dependency

occurs when the value of one (set of) attribute(s) determines the value of a second (set of) attribute(s)

what are the different type of functional Dependency?

Full Functional Dependency: A depends entirely on B. Removing any part of B breaks the dependency.

Partial Functional Dependency: A depends on B, but not entirely. Some attributes in B can be removed while maintaining the dependency.

Transitive Dependency: A depends on B through an intermediary attribute C. C acts as a bridge between A and B in the dependency chain.

Multivalued Dependency: A depends on B, but B can have multiple values for the same A value. Typically encountered in specific scenarios involving multiple values

Explain what is the First Normal Form (1NF)?

A relation is in first normal form if it does not contain any composite or multi-valued attribute.

No multivalued attributes

Every attribute value is atomic

Explain what is the Seconnd Normal Form (2FN)?

A relation in second normal form must be in first normal form and it must not contain any partial dependency

Explain what is Third Normal Form (3NF)?

A relation in third normal form must be in second normal form and must not contain ay transitive dependency