2.0 Relational Data Model

2.1 Explain relational databases

Describe the characteristics of relation scheme:

🔒 Explain Relation Keys 🔒

a. Relation (ELTON)

b. Attribute (FLAVEEN)

c. Domains (FAHIM)

Wall of china for

click to edit

What is Domain?: A domain is the original sets of atomic values used to model data. By atomic value, we mean that each value in the domain is indivisible as far as the relational model is concerned

For example:

✅ The domain of Marital Status has a set of possibilities: Married, Single, Divorced.

click to edit

✅ The domain of Shift has the set of all possible days: {Mon, Tue, Wed…}.

✅ The domain of First Name is the set of character strings that represents names of people.

To facilitate understanding : a domain is a set of acceptable values that a column is allowed to contain. This is based on various properties and the data type for the column

click to edit

d)

b)

g) Primary key (Unique identifier)

click to edit

click to edit

Primary Key ( Khairizatul )

Definition:
A primary key is a special relational database table column (or combination of columns) designated to uniquely identify each table record.
A primary key is used as a unique identifier to quickly parse data within the table. A table cannot have more than one primary key

Candidate Key ( Firdaus )

Definition:
A set of attributes which uniquely identify the tuples ( Recorded Data ) in relation or table :

Example:
{ ID, First name, Last Name }

DESCRIBE THE COMPONENTS OF A RELATIONAL DATABASE STRUCTURAL TERMINOLOGY

Example:
a special ID attribute either ID or First,Last

Define the relational databases and RDMS(Fadzri)

d. Attribute (Column or Field) (ERIC)

f. Primary key (Unique identifier) ( Mellissa )

IDENTIFY THE PROPERTIES OF TABLES IN A RELATIONAL DATABASE(Syairul)

1)A table is perceived as 2D structure composed of rows and columns.
2)Each table row represents a single entity eccurrence within the entity set.
3)Each table column represents an attribute,and each column has a distinct name.
4)Each intersection of a row and column represents a single data value
5)All values in a column must conform to the same data format
6)Each column has a specific range of values known as the attribute domain
7)The order of the rows and columns is immaterial to the DBMS
8)Each table must have an attribute or combination of attributes that uniquely identifies each row
: : :

a common type of database whose data is stored in tables.

image

Properties:

  • It must contain unique values
  • Must not contain null values
  • It should contain minimum fields to ensure uniqueness

Foreign Key ( Ainor )

Definition:
-An attribute whose values match primary key values in the related table.
-foreign keys are used to link data in two relations

EXAMPLE: repeat the ID for the next scedule.

Identify some popular RDBMS packages (MAS)

Stands for "Relational Database Management System." An RDBMS is a DBMS designed specifically for relational databases. Therefore, RDBMSes are a subset of DBMSes. A relational database refers to a database that stores data in a structured format, using rows and columns.

click to edit

e. degree of a relation(number of columns)(dygkuumiafifah)

The degree of a relation is the number of entity types that participate(associate). By seeing an E-R diagram, we can simply tell the degree of a relation i.e the number of an entity type that is connected to a relation is the degree of that relation

A real-world property of an entity type

For example If we have two entity type ‘Customer’ and ‘Account’ and they are linked using the primary key and foreign key. We can say that the degree of relationship is 2 because here two entities are taking part in the relation

Represented by an oval or ellipse in E-R diagram

what-is-the-degree-of-relation-

Types Of attribute

Simple Attribute & Composite Attribute

Single Valued Attribute & Multi-valued Attribute

Stored Attribute & Derived Attribute

EXAMPLE OF RELATIONAL TABLE:
ic-relational-databases-table-example-employees

Based on the number of entity types that are connected we have the following degree of relation

  1. Binary
  1. N-ary
  1. Ternary
  1. Unary

Key Attribute & Non-key Attribute

A simple attribute contains an atomic value which cannot be further divided. It is simply represented by an oval. Example : Roll_no of Student

A Composite attribute can be further divided into other attributes. We represent this in E-R diagram by connecting an oval with oval i.e the composite attribute is also represented by oval and the further divided attribute all also represented by ovals.

For example, Name of a student can be further divided into first name, middle name and last name.

Singlevalued attribute has only one value and it is represented by a simple oval. Meanwhile, Multivalued Attribute has more than one value. It is represented by a double oval.

Example : Section of a Student (Singlevalued Attribute).
A student can have more than one e-mail address (Multivalued Attribute).

Key attribute is used to uniquely identify the entities of an entity type while the other attributes other than the key attribute are the non-key attributes.

Example: We have Roll_no as the key attribute of the ‘Student’ because two students can never has same roll number (Key Attribute).
Class, Section, Age, Name etc, are the non-key attributes (Non-key attribute).

The value of Store attribute should be provided by the user. This attribute is stored and can be used for finding the value of other attributes. For example : Date of Birth(DOB) of Student.

The value of Derived attribute is derived from some other attributes. Example : The value of age attribute is derived from the DOB(date of birth) attribute.

A unique identifier (UID) is any identifier which is guaranteed to be unique among all identifiers used for those objects and for a specific purpose. The concept was formalized early in the development of Computer science and Information systems, in general it was associated with atomic data type.

In relational databases, certain attributes of an entity that serve as unique identifiers are called primary keys.

In a database or spreadsheet, unique identifiers may be designated as a specific column or field to help make sorting and filtering through information easier. This also helps trace information back to a specific user or entity within the system.

maxresdefault

Tuple(Row of Record)(naf)

A single entry in a table is called a Tuple or Record or Row. A tuple in a table represents a set of related data. For example, the above Employee table has 4 tuples/records/rows.

download

An attribute value is an attribute name paired with an element of that attribute's domain, and a tuple is a set of attribute values in which no two distinct elements have the same name. Thus, in some accounts, a tuple is described as a function, mapping names to values

Explain two important integrity rules:

Entity integrity(Lo)

EXPLAIN TWO IMPORTANT INTEGRITY RULES

REFERENTIAL INTEGRITY [LYIANA]

✨Rules states that any value( on the relation of the any side).✨Must match a primary key value in the relation of the one side( or the foreign key can be null).

-Ensure that every relation has a primary key, and data values for the primary key are all valid

-Null: a value that may be assigned to an attribute when na other value applies or when the applicale value is unknow.

-No primary key attribute may be null. All promary key fields MUST have date.

-The primary key is not null, no component of the primary key may be set to null.

-Unique means, value of the primary key must be diffeerent not be same.

-Null is a value, suppose no other value not assign or apply.

-Null is not zero(0)or blank ("").

what-is-data-integrity-entity-integrity-d50c7ca0f31388fa

g. Domain (Pool of values of specific attributes of relation) (ANDY)

c) Cardinality of a Relation(Number of Rows) - Scarlett

Domain restricts the values of attributes in the relation and is a constraint of the relational model. However, there are real-world semantics for data that cannot be specified if used only with domain constraints. We need more specific ways to state what data values are or are not allowed and which format is suitable for an attribute.

Example:
The Employee ID (EID) must be unique or the employee Birthdate is in the range [Jan 1, 1950, Jan 1, 2000]. Such information is provided in logical statements called integrity constraints.


Cardinality” in database design has to do with counting tables and values. With that said, cardinality has three main definitions. It can relate to counting the number of elements in a set, identifying the relationships between tables, or describing how database tables contain a number of values, and what those tables look like in general. Cardinality between tables can be one-to-one, many-to-one or many-to-many.

click to edit

Describe a relation instance(Gracelina kumbau)

A relation instance is a set of the tuples(also known as rows or records)

The relation cardinality is the number of tuples in the relation also degree is the number of fields(or columns)

Candidate Key- A set of attributes in a relation is called a "Candidate Key" if it meets the following criteria:

The values of the set of attributes uniquely identify a tuple
Candidate keys are: (ID) and (FIRST, LAST)
Not unique: (First) and (Last)
The candidate keys are not selected as the primary key are called alternate keys.

Primary Key- Candidate key selected to identify tuples (record) uniquely within relation

-Often a special ID attribute is used as the primary key. Either (ID) or (First, Last) could be used as the primary key.

NULLs & Primary Keys- Missing information can be represented using NULL and NULL indicates an unknown or missing value. The primary keys however cannot contain NULL values. (entity integrity)

Foreign Key - An attribute whose values match primary key values in the related table. Foreign Keys are used to link data in two relations.