Different between data and information

Data refers to raw, unprocessed facts. For example, a list of names and phone numbers is data. Data
on its own has no meaning until it is processed and analyzed.
Eg: customer information in database
Data only does not have any meaning.
Data needs to be processed to turn into information.
Information refers to data that has been processed.
It is the result of analyzing and interpreting data.

4 types of entities about florist store

Customers
Suppliers
Order
Staff
Product
Sales

What is meta data?

Metadata refers to data that describes other data. It also called data about data. It includes
information about the tables such as table name, column names, data types, constraints, relationships
and so on.

What is referential integrity?

Referential integrity is a property of a database system. It prevent the database from inconsistency
and make accuracy.
Referential integrity ensures that if a table contains a foreign key that references a primary key in
another table.

What step you took to make the table into 3NF?

If a table is in 1NF what rules will be followed?

What is meant by case tools? Give four types of activities for which case tools provide support.

1
What is the benefit of normalization?
Normalization is the process of organizing data in a database to reduce data redundancy and improve
data integrity, performance, prevent data inconsistency and reduce storage space required.
Benefits of normalization in DBMS:
Minimizes data redundancy: Normalization eliminates data redundancy by breaking down larger
tables into smaller ones and storing data only once. This not only reduces the amount of disk space
required to store data but also reduces the risk of data inconsistencies.
Improved data integrity: by removing data redundancy, normalization prevents data anomalies and
data inconsistency. This improves data accuracy and reliability.
Simplifies database design: Normalization simplifies the database design by breaking down complex
data structures into smaller, more manageable tables. This makes it easier to maintain and update the
database over time.
Improves database performance: Normalization can improve database performance by reducing the
amount of data that needs to be manipulated (insert, update and delete). This can result in faster
query response times.

It makes easier to extract the required information from the database. This helps to speed up the
performance of queries and reduces the time required to retrieve data.

Many to Many relationship

A many-to-many relationship is a type of relationship in a database where each record in Table A
can be associated with multiple records in Table B, and each record in Table B can be associated
with multiple records in Table A.
This type of relationship can create complexity and redundancy in the database, making it difficult to
maintain data integrity and ensure efficient performance.
To resolve a many-to-many relationship, one common solution is to create a third table called join
table that contain foreign keys from both tables.
For example, consider a database for a university. There may be a many-to-many relationship
between students and classes, where each student can enroll in multiple classes, and each class can
have multiple students. In this case, a join table called "enrollments" can be created, with foreign
keys referencing the students and classes tables. The "enrollments" table may contain columns such
as "student_id" and "class_id".
Classes - Java, c#, web, database, business
Draw all three table with appropriate key.

2
Eg: bookstore
Book, Author
A book can have multiple author
An author can have multiple books

Worker, skill
Student, course
Book, author
Passenger, flight

1NF
The first step of normalization is called first normal form. The step includes in 1NF are:
Identify the entities and attributes.
Eliminate any repeating groups by creating separate tables.
Create a primary key for each table to ensure that each record can be uniquely identified. A primary
key can be a single field or a combination of fields.
Ensure that each attribute contains only atomic (indivisible) values.
By following these steps, we can transform a database into first normal form, which ensures that
each table contains no repeating groups and each record can be uniquely identified by a primary key.
This helps to eliminate redundancy and improve data integrity in the database.

2NF
To make the table into 2NF, the following steps are needed
Ensure that table is already in first normal form.
Each table must have primary key that uniquely identify each row in a table.
Each column must contain single value.
Identify partial dependency to separate the table into multiple tables.
A partial dependency occurs when part of the primary key determines the value of non-key attribute.
Eg: A, B is composite primary key of the table and C, D and E are non-key attributes of that table.
If A determine C, this means that the attribute C is partially dependent on the composite primary key
A, B. At this condition, we need to separate attribute A and C into another table because attribute C
is partially depend on the composite primary key.
Ensure that each non-key attribute is functionally dependent on the entire primary key.
Create relationships: Create relationships between the tables using primary and foreign keys.

3
3NF
Eliminate transitive dependencies
Eg: A is primary key of a table and B, C and D are non-key attributes of that table.
If non-key attribute B is functionally determine on attribute C, this is called transitive dependency.
We need to separate B and C into another table.
Set appropriate primary key and foreign key for these tables.
What are the advantages of DBMS?
A database management system (DBMS) is a software system that manages and organizes data in a
database. The advantages of DBMS include:
Data integration: DBMS allows for integration of data from multiple sources into a single database,
which provides a unified view of data. This makes it easier to manage data and reduces data
redundancy.
Data sharing: DBMS enables multiple users to access the same data simultaneously.
Data consistency: DBMS ensures that data is consistent and accurate by enforcing data integrity rules
and constraints. This prevents data inconsistencies and ensures that data is accurate and reliable.
Data security: DBMS provides data access control mechanism by preventing unauthorized access of
data. Authorized users can access the data in the database. DBMS can provide advanced security
features such as access control and authorization, ensuring that sensitive data is only accessible to
authorized users.
Backup and recovery: DBMS provides backup and recovery mechanisms to protect data from
hardware and software failures.

What are the disadvantages of DBMS?

The following steps should be considered to implement the DBMS:
Cost: DBMS software and hardware can be expensive, especially for larger organizations. Large
storage space are required.
Complexity: DBMS is complex and requires specialized skills and knowledge to design, set up, and
maintain. This can make it difficult for small organizations with limited IT resources to implement
and manage a DBMS.

What is one-to-one relationship?

In database design, a one-to-one relationship is a type of relationship between two tables where each
record in one table is related to only one record in another table.
Eg: person, NIC
Person, Passport

4
Why a company would decide to implement a DBMS?
A company would decide to implement a Database Management System (DBMS) for several
reasons, including:
Data storage and organization: A DBMS can help a company store and organize large amounts of
data in a structured way, making it easier to access, manage, and analyze.
Improved data security: A DBMS can provide better security for sensitive data, such as customer
information or financial records, by implementing access controls.
Data integrity: A DBMS can ensure the consistency and accuracy of data by enforcing rules and
constraints on the data, preventing errors and inconsistencies.
Improved data accessibility: A DBMS can provide simultaneous access to data by multiple users and
sharing of information.
Scalability: A DBMS can handle large amounts of data.

5 examples of data integrity constraints

Data integrity constraints are rules that are enforced by a Database Management System (DBMS) to
ensure that data stored in the database is accurate, consistent, and valid. Here are 5 examples of data
integrity constraints:
Primary key constraint: primary key is uniquely identified each row in a table.
It prevents data duplication in each table.
Foreign key constraint: A foreign key constraint is used to ensure that data in foreign key column
must match the data in primary key column of parent table.
Check constraint: A check constraint is used to ensure that data values meet a specific condition or
set of conditions.
Unique constraint: A unique constraint is used to ensure that a column in a table contains only
unique values.
Not null constraint: A not null constraint is used to ensure that a column in a table contains a value. It
prevents null or empty values from being entered into the database.

Why relationship modeling is important?

Batch processing and consequences?

With batch processing, all transactions were updated once per day, usually during the night.
Delay access to account information: customers can’t get up-to-date account information during the
day because all transactions are updated during the night. Eg: if a customer made a deposit or
withdrawal during the day, they may not have seen the updated balance until the next day, it is
difficult to manage their balance.

5
Overdraft fees and other charges: Because batch processing only updated account information once
per day, there was a risk that customers could overdraw their accounts without realizing it. For
example, if a customer made a withdrawal in the morning and then made another withdrawal later in
the day, they may not have seen the updated balance before making the second withdrawal. This
could result in overdraft fees or other charges, which could be costly for customers.

Student attendance monitoring

A database could assist the attendance monitoring officer in their day-to-day job of monitoring
attendance in several ways. Here is an example:
Tracking attendance records: All of the student record including attendance can be stored in the
database. So officers can easily access student attendance record for each or entire students.

Explain how a typical sql statement is structured and what needs to be included. 3marks
A typical sql statement consist of one or more clauses that specify the operation to be performed.
Select: select clause is used to specify the columns that you want to retrieve from the database.
From: from clause specifies the name of the table from which you want to retrieve the data.
Eg: from table_name
Where: where clause is used to filter the data from one or more tables based on certain condition.
Order by: order by clause is used to sort the result data in alphabetical order ascending or descending
for each column.
Eg: order by name desc

Hardware and software components in DBMS environment

Hardware refers to the physical components of a computer system, such as monitor, motherboard,
CPU, memory, hard drive, and other components. These components are tangible.
The hardware components of a DBMS environment include the server that hosts the database and the
client computer that access the database.
Software refers to the intangible components of a computer system. It includes the programs,
applications, and other digital data that instruct the hardware to perform specific tasks. The DBMS
software is the core software component that manages the database, including creating, modifying,
and deleting database objects. Some popular DBMS software includes MySQL, PostgreSQL, and
MongoDB.

Potential problem with many-to-many relationship

Many-to-many relationship can cause the following problem:
Many-to-many relationship can degrade the performance when manipulate the data in the database.

6
Many-to-many relationship enforces the duplication of data. It can waste the storage space of the
database.
Data inconsistency can be occurred in many-to-many relationship when insert, update or delete the
data.
Many-to-many relationship can cause the data inconsistency. It is difficult to ensure that all instances
of the data are updated or deleted correctly because there are too much data duplication in the
database.
One of the common approach to solve the many-to-many relationship is to use junction table
between these two tables. It contains the primary keys of both tables as foreign keys.
This can help to reduce data duplication, data inconsistency and to improve performance of the
database.

2 examples of what a data dictionary might hold

Data dictionary is also known as meta-data that contains information about the data in a database.
The data dictionary provides a description of the database schema, including the tables, columns,
indexes, and other database objects.
Here are some of the types of data that are typically stored in a data dictionary:
Table Names: The data dictionary stores the names of all tables in the database.
Column Names and Data Types: For each table, the data dictionary stores the names and data types
of each column.
Indexes and Constraints: The data dictionary stores information about indexes and constraints
defined on tables, including their names, columns, and types.
Relationships between Tables: The data dictionary stores information about the relationships
between tables, including foreign key constraints.
Access Permissions: The data dictionary may also store information about user access permissions to
the database objects.
Overall, the data dictionary serves as a valuable reference for database administrators, developers,
and users to understand the structure and contents of the database. By centralizing this information,
the data dictionary helps to ensure that everyone is using the same consistent terminology and can
easily understand the data in the database.

What database functions would be used by the supermarket?

Supermarkets typically use databases to manage their inventory, sales, customer information, and
other business operations. Here are some of the database functions that supermarkets may use:
Inventory management: Supermarkets can use databases to keep track of the stock levels of different
products, manage incoming and outgoing stock, and track the movement of goods within the store.

7
Sales tracking and analysis: Databases can be used to track sales data, including the number of units
sold, revenue generated, and other metrics. This data can be used to analyze sales trends, identify
popular products, and inform pricing and marketing decisions.
Customer relationship management
(CRM): Supermarkets may use databases to store and manage customer data, such as purchase
history, contact information, and loyalty program details. This data can be used to personalize
marketing campaigns, improve customer service, and build customer loyalty.
Employee management: Supermarkets may use databases to manage employee information,
including scheduling, payroll, and performance metrics.
Overall, databases play a critical role in the daily operations of supermarkets, helping them to
manage inventory, track sales data, and provide a better customer experience.

What database functions would be used by the restaurant?

Restaurants use databases to manage a variety of business operations, including ordering, inventory
management, customer management, and more. Here are some of the database functions that a
restaurant may use:
Menu management: Databases can be used to manage the restaurant's menu, including item
descriptions, prices, and availability. This data can be used to generate menu cards, update digital
menus, and ensure that customers receive accurate information about the food.
Ordering and payment processing: Restaurants may use databases to manage online orders and
process payments from customers. This data can be used to track order status, manage inventory
levels, and provide insights into customer behavior.
Inventory management: Restaurants can use databases to manage their inventory, including tracking
the movement of goods within the restaurant, identifying low-stock items.
Customer relationship management (CRM): Restaurants may use databases to store and manage
customer data, such as contact information, preferences, and purchase history. This data can be used
to personalize marketing campaigns, improve customer service, and build customer loyalty.
Employee management: Restaurants may use databases to manage employee information, including
scheduling, payroll, and performance metrics.
Overall, databases play a critical role in the daily operations of restaurants, helping them to manage
menus, inventory, and customer data, and providing a better experience for both employees and
customers.

CASE Tools
Case tools (Computer-Aided Software Engineering tools) are software applications that help
software developers and engineers to design, develop, and maintain software systems. These tools
automate the software development process and help in increasing the efficiency and productivity of
the development team.

8
Case tools can be classified into different categories based on their functionality, such as:
Requirements gathering tools: These tools help in collecting, documenting, and managing the
software requirements.
Analysis and design tools: These tools help in creating the software design by visualizing the
system's structure, components, and functionality.
Implementation tools: These tools help in generating the code, testing, debugging, and integrating the
software components.
Maintenance tools: These tools help in managing and updating the software after its release.
Some examples of popular case tools include Rational Rose, Visual Paradigm, Enterprise Architect,
and MagicDraw. These tools offer a wide range of functionalities and features, such as modeling,
simulation, code generation, and testing, to support the entire software development life cycle.

What is a Relational Database (RDBMS)?

A relational database is a type of database that stores and provides access to data points that are
related to one another. Relational databases are based on the relational model, straightforward way of
representing data in tables. In a relational database, each row in the table is a record with a unique ID
called the key. The columns of the table hold attributes of the data, and each record usually has a
value for each attribute, making it easy to establish the relationships among data points.
To ensure that data is always accurate and accessible, relational databases follow certain integrity
rules. For example, an integrity rule can specify that duplicate rows are not allowed in a table in
order to eliminate the potential for erroneous information entering the database.

The relational model

In the early years of databases, every application stored data in its own unique structure. When
developers wanted to build applications to use that data, they had to know a lot about the particular
data structure to find the data they needed. These data structures were inefficient, hard to maintain,
and hard to optimize for delivering good application performance. The relational database model was
designed to solve the problem of multiple arbitrary data structures.
The relational data model provided a standard way of representing and querying data that could be
used by any application. From the beginning, developers recognized that the chief strength of the
relational database model was in its use of tables, which were an intuitive, efficient, and flexible way
to store and access structured information.
Over time, another strength of the relational model emerged as developers began to use structured
query language (SQL) to write and query data in a database. For many years, SQL has been widely
used as the language for database queries.

Database locking and concurrency

Conflicts can arise in a database when multiple users or applications attempt to change the same data
at the same time. Locking and concurrency techniques reduce the potential for conflicts while
maintaining the integrity of the data.

9
Locking prevents other users and applications from accessing data while it is being updated. In some
databases, locking applies to the entire table, which creates a negative impact on application
performance. Other databases, such as Oracle relational databases, apply locks at the record level,
leaving the other records within the table available, helping ensure better application performance.
Concurrency manages the activity when multiple users or applications invoke queries at the same
time on the same database. This capability provides the right access to users and applications
according to policies defined for data control.

What to look for when selecting a relational database

The software used to store, manage, query, and retrieve data stored in a relational database is called a
relational database management system (RDBMS). The RDBMS provides an interface between users
and applications and the database, as well as administrative functions for managing data storage,
access, and performance.
Several factors can guide your decision when choosing among database types and relational database
products. The RDBMS you choose will depend on your business needs. Ask yourself the following
questions:
What are our data accuracy requirements? Will data storage and accuracy rely on business logic?
Does our data have stringent requirements for accuracy (for example, financial data and government
reports)?
Do we need scalability? What is the scale of the data to be managed, and what is its anticipated
growth? Will the database model need to support mirrored database copies (as separate instances) for
scalability? If so, can it maintain data consistency across those instances?
How important is concurrency? Will multiple users and applications need simultaneous data access?
Does the database software support concurrency while protecting the data?
What are our performance and reliability needs? Do we need a high-performance, high-reliability
product? What are the requirements for query-response performance? What are the vendor’s
commitments for service level agreements (SLAs) or unplanned downtime?

What is meant by real-time in DBMS?

In DBMS, real time refers to ability to response transactions or queries immediately as they occur,
without delaying. Real-time DBMSs can support various types of transactions, such as read-only
transactions, update transactions, with different levels of concurrency control and transaction
isolation.
Real-time DBMSs are commonly used in applications such as financial trading systems, airline
reservation systems, and telecommunications networks, where quick and reliable data access and
processing is critical.

10