Information Management - Finals

Relational Model

Introduced by E.F. Codd in 1970, it uses predicate logic and set theory to represent data in tables (relations).

Table (Relation)

A two-dimensional structure composed of rows (tuples) and columns (attributes).

Tuple

A single row in a table; represents one record or entity.

Attribute

A column in a table; represents a specific property of an entity.

Primary Key

An attribute (or set of attributes) that uniquely identifies each row in a table and cannot contain NULL values

Foreign Key

An attribute in one table that references the primary key of another table to maintain referential integrity

Superkey

Any combination of attributes that uniquely identifies rows in a table

Composite Key

A key composed of more than one attribute.

Entity Integrity

Ensures that every table has a primary key and that key values are unique and not NULL.

SELECT (σ)

Retrieves specific rows that satisfy a condition.

PROJECT (π)

Retrieves specific columns

JOIN (⨝)

Combines related tuples from two tables based on a condition

SQL

Structured Query Language used to manage and manipulate relational databases

CREATE TABLE

SQL command to create a new table with specified columns and data types

ALTER TABLE

SQL command to modify an existing table by adding, modifying, or deleting columns

DROP TABLE

Deletes a table and its data from the database.

NOT NULL

Constraint ensuring a column cannot have NULL values

UNIQUE

Ensures that all values in a column are different.

CHECK

Ensures that values in a column meet a specified condition

DEFAULT

Assigns a default value if no value is provided during insertion.

Entity

A person, place, event, or object about which data is collected and stored

Attribute (Modeling)

A characteristic of an entity.

Relationship

An association between entities, such as 1:1, 1:M, or M:M.

Hierarchical Model

An early database model using a tree-like structure with parent-child relationships.

Network Model

Uses a graph structure allowing many-to-many relationships

Entity-Relationship Model (ERM)

Uses diagrams to show entities and relationships; introduced by Peter Chen

Object-Oriented Model

Combines data and relationships in one structure (object); supports inheritance, encapsulation.

XML

markup language used to describe and manipulate data elements

Big Data

Refers to managing large amounts of varied and fast-growing data (Volume, Velocity, Variety).

NoSQL

A type of database that handles unstructured/semi-structured data; does not follow the relational model; supports high availability and scalability.

Information

it refers to transformed data

Data redundancy

when the same data is stored and necessarily, this is refer to as:

Metadata

this is referred to as data about data in which and user data is integrated

Workgroup database

this type of database supports relatively small number of users

Analytical database

this type of database focuses primarily on storing historical data and business metrics used for tactical decision making

Data

these are raw facts that have not yet been processed to reveal their meaning to the end users

General-purpose database

this type of database contains a wide variety of data used in multiple disciplines

DBMS (Database Management System)

this is a collection of programs that manage the database structure and controls access to the data stored in the database

Normalization

what is the process of assigning attributes to entities to minimize data redundancy

Row

it is a set of data about a specific entity

Relationship

it is used to describe the associations among entities

Domain

it is used to specify the range of values in a column

Rectangle

In an ERD, which shape represents an entity

3NF

which normal form is considered the highest level necessary for most applications

Diamond

in the chen model, which shape represents the relationship between entities

Oval

in the original chen notation, which shape is used in representing the attributes of an entity

Table Name

in a crows foot notation, what is the written above all the attributes

Underline It

How can EMP_ID be identified as the primary key in the Chen notation

1NF

to eliminate repeating groups, which normal form must be reached

Search

which of the following operations DOES NOT cause a data anomaly

Deletion

Insertion

Search

Update

ABC U XYZ

which of the following combines the raw of the tables name ABC and XYZ

ABC U XYZ

ABC n XYZ

ABC - XYZ

ABC x XYZ

IN

which operator is used with WHERE to check whether a value matches any value within a given list

LIKE

this operator is used with WHERE to determine whether a value matches a given string pattern

BETWEEN

this operator is used with WHERE to check whether a value is within a range

DISTINCT

this option is used with SELECT to retrieve unique values from columns in a table

IS NULL

this operator is used with SELECT to determine whether a field is empty or not

ALTER TABLE students ADD Age int;

which statement adds the Age column to students table

ADD Age int ALTER TABLE students;

ALTER TABLE students ADD Age int;

SELECT

which of the following is NOT an SQL operator

BETWEEN

IN

LIKE

SELECT

<>

which of the following is NOT a compound operator

+=

/=

<>

%=

-

which of the following is NOT a comparison operator

=

<

>

-

DEFAULT

which of the following is NOT a logical operator

BETWEEN

DEFAULT

IN

LIKE

COPY

which of the following is NOT a basic manipulation command in SQL

COPY

DELETE

INSERT

UPDATE

Aggregate Functions

performs a calculation on a set of values and returns a single value

MIN

minimum attribute value encountered in a given

column

MAX

maximum attribute value encountered in a given

column

COUNT

number of rows containing non-null values

SUM

sum of all values for a given column

AVG

arithmetic mean (average) for a specified column

GETDATE

returns the current date and time

DATEADD

adds a number of selected time/date periods to a date then returns the date

DATEDIFF

returns the difference between two (2) dates

ABS

returns the absolute value of a number

ROUND

rounds a number to a specified number of decimal places

CEILING

returns the smallest integer value that is greater than or equal to a number

FLOOR

returns the largest integer value that is less than or

equal to a number

CONCAT

joins two or more strings together

LEN

returns the number of characters in a string

TRIM

removes the spaces or specific characters from start or end of a string

JOIN

clause combines rows from two or more tables based on a

common column.

INNER JOIN

returns rows that have matching values in both tables

LEFT JOIN

returns all rows from the left table and the matched records from the right table.

RIGHT JOIN

returns all rows from the right table and the matched records from the left table.

FULL JOIN

returns all records when there is a match on

either the left or right table.