W1 - 5 Flashcards

INFS1603 L1-5 combined flashcards... COOOKED

Data vs. Information

Data is raw, unorganized facts that need to be processed. Information is data that has been processed and organized in a meaningful way.

Database Management System (DBMS)

Software for managing and organizing databases.

Entity-Relationship Modeling (ERM)

A technique for visually representing the structure of a database.

Entity Type

A category of objects or concepts with similar properties.

Entity Instance

A specific occurrence of an entity type.

Attribute

A characteristic or property of an entity.

Value

A specific data point for an attribute.

Logical Model

A detailed representation of the database structure, converted from a conceptual model, ready for database implementation.

Relational Model

A database model that organizes data into tables (relations) with rows (tuples) and columns (attributes).

Normalization

The process of organizing data in a database to reduce redundancy and improve data integrity.

SQL (Structured Query Language)

A standard language for accessing and manipulating databases.

Data Definition Language (DDL)

The subset of SQL commands used to define database schemas, such as creating, altering, and dropping tables.

Data Manipulation Language (DML)

The subset of SQL commands used to manipulate data, such as selecting, inserting, updating, and deleting data.

Entity Clustering

Grouping related entities together in an ER diagram to simplify the model.

Relation

A two-dimensional table in a relational database.

Tuple

A row in a relation, representing a single instance of the entity.

Attribute

A column in a relation, representing a characteristic of the entity.

Key

An identifier for a row of data in a relation.

Schema

The structure of a relation, including the name of the relation and its attributes.

Database Schema

The set of schemas for all relations in a database.

Data Dictionary (Metadata)

A repository of information about the database, including table purposes, column data types, constraints, and indexes.

Primary Key

A unique identifier for each row in a table.

Candidate Key

Any set of one or more columns whose combined values are unique among all occurrences in a table.

Alternate (Secondary) Key

A candidate key that is not chosen as the primary key.

Foreign Key

A set of one or more columns in a table that refers to the primary key of another table.

Entity Integrity

A rule ensuring that all primary key entries are unique and no part of a primary key may be NULL.

Referential Integrity

A rule ensuring that a foreign key value either matches an existing primary key value in the referenced table or is NULL.

Domain Integrity

A rule ensuring that data values conform to the column's predefined data type and constraints.

DDL (Data Definition Language)

SQL commands that define and modify database structures (e.g., CREATE, ALTER, DROP).

DML (Data Manipulation Language)

SQL commands used to retrieve, insert, update, and delete data (e.g., SELECT, INSERT, UPDATE, DELETE).

TCL (Transaction Control Language)

SQL commands that manage transaction consistency within the database (e.g., COMMIT, ROLLBACK, SAVEPOINT).

Transaction

A logical unit of work that consists of one or more SQL statements.

CREATE SCHEMA

SQL command to create a new database schema.

CREATE TABLE

SQL command to create a new table in a database.

SELECT

SQL command to retrieve data from one or more tables.

FROM

Specifies the table(s) from which to retrieve data in a SELECT statement.

WHERE

Filters the rows of data based on provided criteria in a SELECT statement.

ORDER BY

Sorts the final query result rows in ascending or descending order based on the values of one or more columns in a SELECT statement.

GROUP BY

Groups the rows of data into collections based on sharing the same values in one or more columns in a SELECT statement.

HAVING

Filters the groups formed in the GROUP BY clause based on provided criteria in a SELECT statement.

Relationship Degree

The number of entities involved in an association.

Relationship Connectivity

The types of association between entities.

Relationship Cardinality

The numerical constraints of the relationship between entities.

BETWEEN

Defines a range of values for a WHERE clause condition.

IS NULL

Checks for NULL values in a column for a WHERE clause condition.

LIKE

Matches a specified pattern in a WHERE clause condition.

IN

Specifies multiple possible values for a column in a WHERE clause.

EXISTS

Checks for the existence of rows in a subquery.

Entity-Relationship Modeling

A modeling technique used in database design.

Crow’s Foot notation

A visual representation standard used in database design, as specified in the textbook.

Attribute

A characteristic or property of an entity.

Relationship Degree

The number of entities associated with a relationship.

Logical Model

Converting Conceptual model to detailed Logical Model ready for DB implementation.

Weak Entity

An entity that cannot exist without another parent entity.

Strong Entity

An entity that can exist apart from parent entities.

Strong (identifying) relationships

Relationships where the primary key of the child entity contains a primary key component of the parent entity.

Weak (non-identifying) relationship

Relationships where the primary key of the child entity does not contain a primary key component of the parent entity.

Composite Entity

An entity that builds a bridge between original entities and contains attributes singular to the relationship.

M:N relationships

Relationships where many instances of one entity can relate to many instances of another entity.

1:M relationships

Relationships where one instance of an entity relates to many instances of another entity.

Supertype

A more generic entity type.

Subtype

A more specific entity type compared to its supertype.

Specialisation Hierarchy

Depicts arrangement of higher-level supertypes and lower-level subtypes

Generalisation Process

Defining a general entity type from a set of specialised entity types

Specialisation Process

Defining one or more subtypes of the supertype

Disjoint (non-overlapping) subtypes

Contain a unique subset of the supertype entity set.

Overlapping subtypes

Contain non-unique subsets of the supertype entity set

Convert Logical Model to Relational Model

Relational Modelling and SQL

Natural key

Real-world, generally accepted identifier for real-world objects

Normalisation

The process of correcting table structures to minimise data redundancies.

Insertion Anomaly

Adding new rows forces user to create duplicate data.

Deletion Anomaly

Deleting rows may cause a loss of data that would be needed for other future rows.

Modification (Update) Anomaly

Changing data in a row forces changes to other rows because of duplication.

Normal Form

A state of a relation determined by applying rules on Functional Dependency.

Functional Dependency (FD)

Semantic restriction that some values for a relation are not possible in reality

Determinant

Any attribute(s) whose value determines other values within a row.

Dependant

Attribute whose value is determined by a determinant.

Partial dependency

Determinant is only part of primary key or candidate key, and dependant is a non-prime attribute.

Transitive dependency

X -> Y; Y -> Z; X is primary key. X determines the value of Z via Y, i.e., X -> Z

First Normal Form (1NF)

Table format, no repeating groups, and PK identified.

Second normal form (2NF)

1NF and no partial functional dependencies.

Third normal form (3NF)

2NF and no transitive functional dependencies.

Boyce-Codd normal form (BCNF)

3NF and every determinant is a candidate key (special case of 3NF).

Cross Join

Select all possible combinations of tuples in R with tuples in S, “𝑅 ∗ 𝑆”

Inner Join

Returns combined tuples from two relations that have the same value for a defined attribute (match on the attribute).

Natural Join

Joins tuples based on all attributes with identical names in the two relations, i.e., all common columns, and find matching values in each pair of common columns.

Full Outer Join

Joins tuples from two tables that match on a defined attribute. If no match, the combined row will still appear with missing attributes as NULL (both tables are preserved)

Left Outer Join

Left table is completely preserved. If no match, the attributes from the right side will contain NULL values.

Right Outer Join

Right table is completely preserved. If no match, the attributes from the left side will contain NULL values.

Data Definition Language

Defines the tables in a database.

Data Manipulation Language

Queries/updates the tables in a database.

1:M Relationship Foreign Key Placement

Place the PK of the '1' side (parent entity) as a Foreign Key in the 'M' side (child entity).

1:1 Non-Recursive Relationship (Optional Side)

Place FK on the optional side of the relationship.

Fan Trap

One entity in two 1:M relationships to other entities, leading to a link between the other two entities.

SQL OR Operator

The entire condition is true if either one of the conditions is true.

SQL AND Operator

The entire condition is true only if both conditions are true.

SQL NOT Operator

Opposite of a condition - true becomes false; false becomes true.

SQL IS NULL

Check whether an attribute value is null.

SQL IS NOT NULL

Check whether an attribute value is NOT null.

SQL BETWEEN

Define range limits.