Information Management | Midterm Exam Reviewer

Structured Query Language

What does SQL means?

1. One-to-one

2. One-to-many

3. Many-to-many

Types of Relationships

One-to-One

When each entity in each entity set can take part only ONCE in the relationship

One-to-Many

When entities in one entity set can take part ONLY ONCE in the relationship set and entities in other entity sets can take part MORE THAN ONCE in the relationship set

Many-to-Many

When entities in all entity sets can take part MORE THAN ONCE in the relationship

1. Physical or Internal Level

2. Logical or Conceptual Level

3. View or External Level

The Three (3) Level of Abstraction:

Physical or Internal Level

It is the representation of the entire database as “seen” by the DBMS.

Logical or Conceptual Level

• It represents a global view of the entire database by the entire organization.

• Also known as the conceptual schema, it is the basis of the and high-level description of the main data objects.

View or External Level

• It is the end-users view of the data environment.

• It refers to the people who use the application programs to manipulate the data and generate information.

1. Conceptual Data Model

2. Logical Data Model

3. Physical Data Model

Types of Data Models:

Conceptual Data Model

• This represents high-level concepts and relationships between data entities.

• It's independent of any specific technology or implementation.

Logical Data Model

• Consists of tables, columns, and keys (Primary and Foreign)

• This further refines the conceptual model by defining the attributes, keys, and relationships between entities in a more detailed and implementation-oriented manner.

Physical Data Model

• Consists of schema, data type, and data stored.

• This describes how data is stored in the database, including details such as data types, indexing, and storage optimization.

• It's closely tied to the specific DBMS being used.

1. Entities

2. Attributes

3. Relationships

4. Primary Key (PK)

5. Foreign Key (FK)

6. Constraints

7. Cardinality

Components of Data Models:

Entities

• Objects or concepts that represent data (e.g., Customer, Order).

• A person, place, thing, or event about which data will be collected and stored

• It may be an object with a physical existence - a particular person, car, house, or employee - or it may be an object with a conceptual existence - a company, a job, or a university course.

Attributes

• Characteristics of entities (e.g., CustomerName, OrderDate).

• Describes the properties of an object (for Object-Oriented Model)

Relationships

• Connections between entities (e.g., Customer places Order).

• Describes an association among entities

Primary Key (PK)

• A unique identifier for each record in a table

• It 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 (FK)

A reference to a primary key in another table, establishing relationships.

Constraints

• Refers to a rule or condition applied to the data in a database table. 

• These are used to enforce data integrity, ensuring that the data stored in the database meets certain requirements or follows specific rules (e.g., PK, FK, UNIQUE, CHECK, NOT NULL).

• It help maintain the accuracy, consistency, and reliability of the data.

Cardinality

• Defines the number of occurrences in a relationship

• The number of times an entity of an entity set participates in a relationship set

1. Data Redundancy

2. Data Integrity Issue

3. Limited Data Access

4. Scalability Problems

5. Security Concerns

6. Difficulties in Data Analysis

7. Lack of Standardization

Limitation of File-Based Approach:

Data Redundancy

The same data may be stored in multiple files, leading to duplication.

Data Integrity

Ensuring data remains accurate and consistent across different files is challenging.

Limited Data Access

File-based systems often do not support simultaneous access by multiple users effectively.

Scalability Problems

As the volume of data increases, managing it across numerous files becomes cumbersome.

Security Concerns

File-based systems typically lack robust security features.

Difficulties in Data Analysis

Analyzing data stored in separate files can be complex and time-consuming.

Lack of Standardization

Different files may be formatted or structured differently.

NoSQL

It is a large scale distributed system that stores structured and unstructured data.

1. Uber

2. LinkedIn

3. Netflix

4. Youtube

5. Facebook and Messenger

6. Google

7. Accenture

Famous applications that use NoSQL:

1. Single-user Database

2. Desktop Database

3. Multiuser Database

4. Workgroup Database

5. Enterprise Database

6. Centralized Database

7. Distributed Database

8. Cloud Database

9. General-purpose Database

Types of DBMS:

Single-user Database

A type of database that supports only user at the time.

Desktop Database

A single user that runs on a personal computer.

Multiuser Database

A type of database that supports multiple users at the same time.

Workgroup Database

A type of database that supports a relatively small number of users or a specific department within the organization.

Enterprise Database

A type of database that is used by the entire organization and support many users across many department.

Centralized Database

A type of database that supports data located at a single site.

Distributed Database

A type of database that supports data distributed across several different sites.

General-purpose Database

A database that contains a wide variety of data used in multiple discipline.

Cloud Databases

• Databases that are hosted in the cloud, allowing for scalability, flexibility, and accessibility from anywhere with an internet connection.

• They can be relational or NosQL.

• A database that is created and maintained using cloud services, such as Microsoft Azure or Amazon AWS.