Relational Database 157a (1)

Question 1

What is Data?

Answer 1

• data refers to raw facts, observations, or information that can be collected, stored, and analyzed.
• it can take various forms, including numbers, text,images, audio, and more
• It is the foundation of knwoledge

Question 2

What are the types of data?

Answer 2

Structured,Unstuctured, and Semi-Structured

Question 3

What is Structured Data?

Answer 3

• can be described according to a formal logical data model
• ability to express integrity rules and enforce correctness of data also facilitates searching, processing and analyzing the data
• ex. number, name, address, email of a student

Question 4

What is Unstructured Data?

Answer 4

• No finer grained components in a file or series of characters that can be interpreted in a meanigful way by a DBMS or application
• Ex. invoices, audio, records, emails, audio, weather data, sensor data
• total volume of unstrucutred data surpasses that of structured data

Question 5

What is semi structured data?

Answer 5

Semi-structured data
○ data which does have a certain structure, but the structure may be very irregular or highly
volatile
○ E.g., individual users’ webpages on a social media platform, or resume documents in a human
resources database

Question 6

Where do we store data?

Answer 6

●SSDs
● HDDs
● PC registers
● Files

These are all considered databases

Question 7

What is a database?

Answer 7

● A collection of files storing related data.
● A Database is a large, organized collection of related data
● A Database Management System(DBMS) is the software designed to store, manage,
and facilitate access to large collections of related data
● The combination of a DBMS and a database is often called a database system

examples:
Accounts database;
Payroll database
Amazon’s products database
Airline reservation database

Question 8

What is DBMS?

Answer 8

• A big program that allows us to manage efficiently a large database and allows it
  to persist over long periods of time.
  Examples of DBMSs
  – Oracle, IBM DB2, Microsoft SQL Server, Vertica, Teradata
  – Open source: MySQL (Sun/Oracle), PostgreSQL, AsterixDB
  – Open source library: SQLite

Question 9

What are some DBMS features?

Answer 9

Supports massive amounts of data
–Giga/tera/peta bytes
–Far too big for main memory
Persistent storage
–Programs update, query, manipulate data.
–Data continues to live long after program finishes.
Efficient and convenient access
–Efficient: do not search entire database to answer a query.
–Convenient: allow users to query the data as easily as possible.

Question 10

What is a database model?

Answer 10

●A database model is comprised of different data models, each describing the
data from different perspectives
● A data model provides a clear and unambiguous description of the data items,
their relationships and various data constraints from a particular perspective
● Database model or database schema provides the description of the database
data at different levels of detail and specifies the various data items, their
characteristics and relationships, constraints, storage details, etc.
○ specified during database design and not expected to change too frequently
○ stored in the catalog

ex.
Student (number, name, address, email)
Course (number, name)
Building (number, address)

Question 11

What is Database State?

Answer 11

Database state represents the data in the database at a particular moment
○ also called the current set of instance\
○ typically changes on an ongoing basis

Question 12

What are the three famous layers of architecture?

Answer 12

• External
• Conceptual/Logical
• Internal

Changes in one layer should have no to minimal impact on the others
○ Physical data independence
○ Logical data independence

Question 13

What is the External Layer?

Answer 13

● External layer (View level)
○ External data model which includes views
○ Used to control data access and enforce security

Changes in one layer should have no to minimal impact on the others
○ Physical data independence
○ Logical data independence

Question 14

What is the conceptual logical layer?

Answer 14

Conceptual\logical layer (Logical level)Contains the conceptual and logical
data models
○ E.g.,tables

Changes in one layer should have no to minimal impact on the others
○ Physical data independence
○ Logical data independence

Question 15

What is the internal layer?

Answer 15

Internal layer (Physical level)
○ Includes the internal data model
○ E.g., Index

Changes in one layer should have no to minimal impact on the others
○ Physical data independence
○ Logical data independence

Question 16

Who are some of the different database users?

Answer 16

• Information architect designs the conceptual data model–closely interacts
  with the business user
  ● Database designer translates the conceptual data model into a logical and
  internal data model
  ● Database administrator(DBA) is responsible for the implementation and
  monitoring of the database
  ● Application developer develops database applications in a programming
  language such as Java or Python
  ● Business user will run these applications to perform specific database
  operations

Question 17

Advantages of DB Systems and DB Management?

Answer 17

Data Independence
● Database Modelling
● Managing Structured, Semi-Structured and Unstructured Data
● Managing Data Redundancy
● Specifying Integrity Rules
● Concurrency Control
● Backup and Recovery Facilities
● Data Security
● Performance Utilities

Question 18

What are the different types of databases?

Answer 18

• Centralized
• Distributed
• NoSql
• Cloud
• Relational
• Network
• Object oriented
• hierarchical

Question 19

Hierarchical DBMS?

Answer 19

● Data in this type of database is structured hierarchically.
● One-to-one and a one-to-many relationship between two/more types of data.
● Can access the records by navigating down through the data structure using pointers.
● The “root” in the structure is a single table in the database and other tables act as the branches
flowing from the root.
● A relationship in this database model is represented by the term parent/child.
● The windows registry used in Windows XP is an example of a hierarchical database.
Drawbacks:
● Storing a record in a child table without a related parent
entry poses difficulties, requiring users to record an
additional entry in the parent table.
● This database lacks support for complex
relationships and faces redundancy issues,
leading to inaccurate information from
inconsistent data recording across multiple sites.

Question 20

Network DBMS?

Answer 20

● Supports many-to-many relations.
● Fast data access
● There are two fundamental concepts of a network model:
1. Records contain fields that need hierarchical organization.
2. Sets are used to define one-to-many relationships between records that contain one
owner, many members.
Drawbacks:
● A user must be very familiar with the structure of the
database to work through the set structures.
● Updating inside this database is a tedious task.
Changing a set structure impacts application
programs relying on it. Modifications to the
structure require corresponding updates in
programreferences

Question 21

Object Oriented DBMS?

Answer 21

● Represents information in the form of objects as used in object-oriented programming.
● Capable of handling a large variety of data types
● Improved performance
● Enriched modelling capabilities
● Some POPULAR EXAMPLES of OODBMs are TORNADO, Gemstone, ObjectStore, GBase,
VBase, InterSystems Cache, Versant Object Database, ODABA, ZODB, Poet. JADE, and
Informix.
Drawbacks:
● An object-oriented database is more expensive to develop.
● Most organizations are unwilling to abandon and convert
from those databases.

Question 22

Relational DBMS?

Answer 22

● Based on the relational model introduced by E.F. Codd.
● Standard relational databases enable users to manage predefined data relationships across
multiple databases.
● Relational databases use key fields in each table to uniquely identify rows and establish
connections between different tables.
● Simplicity
● Ease of Data Retrieval
● Data Integrity
● Flexibility

Database system that stores and retrieves data in a tabular format.
● Organized in the form of rows and columns.
● Capability to handle larger magnitudes of data

● Relational Database Management Systems.
● Based on the relational model introduced by E.F. Codd
● A program that allows us to create, delete, and update a relational database.
● RDBMS languages are Oracle SQL, MySQL, etc.
● Maintains data integrity

Question 23

What is DDL?

Answer 23

○ It is used to define database structure or pattern.
○ It is used to create schema, tables, indexes, constraints, etc. in the database.
○ Using the DDL statements, you can create the skeleton of the database.
○ Stores the information of metadata like the number of tables and schemas, their names, indexes, columns
in each table, constraints, etc.
Some tasks that come under DDL:
○ Create: It is used to create objects in the database.
○ Alter: It is used to alter the structure of the database.
○ Drop: It is used to delete objects from the database.
TIidlldfb

Question 24

t is

What is DML(Data Manipulation Language)?

Answer 24

○ It is used for accessing and manipulating data in a database.
○ It handles user requests.
Some tasks that come under DML:
○ Select: It is used to retrieve data from a database.
○ Insert: It is used to insert data into a table.
○ Update: It is used to update existing data within a table.
○ Delete: It is used to delete all records from a table.
○ Merge: It performs UPSERT operation, i.e., insert or update operations.
○ Call: It is used to call a structured query language or a Java subprogram.

Question 25

What is DCL(Data control langugage)?

Answer 25

○ It is used to retrieve the stored or saved data.
○ The DCL execution is transactional. It also has rollback parameters.
Some tasks that come under DCL:
○ Grant: It is used to give user access privileges to a database.
○ Revoke: It is used to take back permissions from the user.

Question 26

what is TCL(Transacti)?on Control Language)?

Answer 26

○ It is used to run the changes made by the DML statement.
○ Deals with internal database transactions.
○ TCL can be grouped into a logical transaction.
Some tasks that come under TCL:
○ Commit: It is used to save the transaction on the database.
○ Rollback: It is used to restore the database to original since the last Commit.

Question 27

What are Relational Keys?

Answer 27

Relation Key: These are basically the keys that are used to identify the rows uniquely or
also help in identifying tables. These are of the following types. (More details in upcoming
lectures)
● Primary Key
● Candidate Key
● Super Key
● Foreign Key

Question 28

Advantages of RDBMS?

Answer 28

● Easy to manage: Each table can be independently manipulated without affecting others.
● Security: It is more secure consisting of multiple levels of security. Access of data shared
can be limited.
● Flexible: Updating of data can be done at a single point without making amendments at
multiple files. Databases can easily be extended to incorporate more records, thus providing
greater scalability. Also, facilitates easy application of SQL queries.
● Users: RDBMS supports client-side architecture storing multiple users together.
● Fault Tolerance: Replication of databases provides simultaneous access and helps the
system recover in case of disasters, such as power failures or sudden shutdowns.
● Easy Data Handling:
○ Data fetching is faster because of relational architecture.
○ Data redundancy or duplicity is avoided due to keys, indexes, and normalization
principles.
○ Data consistency is ensured because RDBMS is based on ACID properties for data
transactions.

Question 29

4 ACID Properties in DBMS

Answer 29

Atomicity
Consistency
Isolation
Durability

Question 30

Atomicity

Answer 30

Entire Transaction takes place at once or doesn’t happen at all.
* Commits: all the changes are made
* Aborts: no changes are made

Transaction T consisting of T1 and T2: Transfer of 100 from account X to account Y.

Question 31

Consitency

Answer 31

The tables must always satisfy user-specified integrity constraints.
(E.g., Account number is unique, Sum of debits and of credits is 0)
* How DBs support consistency? Split responsibility
i. Programmer knows needed logic. Will assert a TXN will go from one consistent
state to a consistent state
ii. System asserts the TXN is atomic (e.g., if EXCEPTION, rolls back)

Question 32

Isolation

Answer 32

Multiple transactions can occur concurrently without leading to the inconsistency of
the database state.
● Transactions occur independently

Question 33

Durability

Answer 33

• The effect of a Transaction must persist after the Transaction
• And after the whole program has terminated
• And even if there are power failures, crashes, etc.
• ⇒ Write data to durable IO (e.g., disk)

Question 34

Why we have Keys in DB?

Answer 34

● A Key is an attribute or a set of attributes in a relation that identifies a tuple
(record) in a relation.
● The keys are defined in a table to access or sequence the stored data quickly
and smoothly.
● They are also used to create relationship between different tables.
● They help you uniquely identify a row in a table by a combination of one or
more columns in that table.

Question 35

What are the different keys?

Answer 35

1. Primary Key
2. Candidate Key
3. Super Key
4. Foreign Key
5. Alternate Key
6. Composite Key
7. Unique Key

Question 36

Primary Key

Answer 36

● A primary key is a column of a table or a set of columns that helps to identify
every record present in that table uniquely.
● A relation is allowed to have only one primary key.
● The value of primary key can never be NULL.
● The value of primary key must always be unique.
● The values of primary key can never be changed i.e. no updation is possible.
● The value of primary key must be assigned when inserting a record.

It helps to improve performance of database by creating unique index on it.
● It is used to update and delete records in the table.
● Primary keys help to enforce data integrity by ensuring that no duplicate
records exist in a table.

Question 37

Candidate Key

Answer 37

○ A candidate key is an attribute or set of attributes that can uniquely identify a
tuple.
○ Except for the primary key, the remaining attributes are considered a
candidate key. The candidate keys are as strong as the primary key.
○ Removing any attribute from the candidate key fails in identifying each tuple
uniquely.
○ The value of candidate key must always be unique.
○ The value of candidate key can never be NULL.
○ It is possible to have multiple candidate keys in a relation.
○Thoseattributeswhichappearsinsomecandidatekeyarecalledasprime

Question 38

Super Key

Answer 38

● A super key is a set of attributes that can identify each tuple uniquely in the
given relation.
● A super key is not restricted to have any specific number of attributes.
● Thus, a super key may consist of any number of attributes.
● All the attributes in a super key are definitely sufficient to identify each tuple
uniquely in the given relation but all of them may not be necessary.
● Adding zero or more attributes to the candidate key generates the super key.
● A candidate key is a super key but vice versa is not true.
● Super Key values may also be NULL.

Uses of Super Key
● Creates clustered index on primary key for efficient data retrieval.
● Allowing for efficient searching and sorting of data in the table.
● Improving query performance by using a super key as an index.
● Identifying a specific tuple in a table for update or deletion.
● Establishes relationships between table through foreign keys.

Question 39

Foreign

Answer 39

● Foreign keys are the column of the table used to point to the primary key of
another table.
● An attribute ‘X’ is called as a foreign key to some other attribute ‘Y’ when its
values are dependent on the values of attribute ‘Y’.
● The attribute ‘X’ can assume only those values which are assumed by the
attribute ‘Y’.
● Here, the relation in which attribute ‘Y’ is present is called as the referenced
relation.
● The relation in which attribute ‘X’ is present is called as the referencing relation.
● The attribute ‘Y’ might be present in the same table or in some other table
● Foreign key references the primary key of the table.
● Foreign key can take only those values which are present in the primary key of
the referenced relation.
● Foreign key may have a name other than that of a primary key.
● Foreign key can take the NULL value.
● There is no restriction on a foreign key to be unique.
● In fact, foreign key is not unique most of the time.
● Referenced relation may also be called as the master table or primary table.
● Referencing relation may also be called as the foreign table.

Question 40

Alternate Key

Answer 40

● The candidate key other than the primary key is called an alternate key.
● All the keys which are not primary keys are called alternate keys.
● Alternate key is also called “Secondary Key”.
● It contains two or more fields to identify two or more records.
● The alternate key may or may not exist.

Question 41

Composite Key

Answer 41

● Composite key is the one having combination of more than one attribute to
uniquely determine the records/tuples 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.

Question 42

Unique key

Answer 42

● A unique key is a set of one or more attribute that can be used to uniquely
identify the records in table.
● Unique key is similar to primary key but unique key field can contain a “Null”
value but primary key doesn’t allow “Null” value.
● Once assigned, its value can not be changed i.e. it is non-updatable.