DBMS

The Relational Database Model is based on:

  • Predicate Logic: assertion can be verified as either true or false.

  • Set Theory- sets or groups of things are used as the basis for data manipulation.

Components of Relational Model:

  1. a logical data structure

  2. a set of integrity rules

  3. a set if operations

Characteristics of a table:

  • A table (relation) is as a two-dimensional structure composed of rows and columns.

  • Each table row (tuple) represents data about an entity.

  • Each table column represents an attribute, and each column has a distinct name.

  • Each intersection of a row and column represents a single data value.

  • All values in a column must conform to the same data format. Each column has a specific range of values known as the attribute domain.

  • The order of the rows and columns is not important in a DBMS.

Types of Keys:

  1. Super key- an attribute or combination of attributes that uniquely identifies any row in the table.

  2. Candidate key- a super key without any unnecessary attributes.

  3. Primary key- a candidate key selected to uniquely identify all other attribute values in any given row; cannot contain null entries.

  4. Foreign key- an attribute or combination of attributes in one table whole values must either match the primary key in another table or be null.

  5. Secondary key- an attribute or combination of attributes used strictly for data retrieval purposes.

A key is an attribute or group of attributes that determines the values of other attributes. For example, an invoice number identifies all of the invoice attributes, such as the invoice date and the customer name.

Determination- is the state in which knowing the value of an attribute makes it possible to determine the value of another. It is based on the relationships among the attributes.

Functional dependence- means that the value of one or more attributes determines the value of one or more other attributes.

The attribute whose value determines another is called the determinant or the key. The attribute whose value is determined by the other attribute is called the dependent.

Fundamentals

Entity Relationship Modeling

Exactly one One or many

· The Entity Relationship Model (ERM) is a data model that describes relationships among entities at the conceptual level with the help of ER diagrams.

· The Entity Relationship Diagram (ERD) is a diagram that depicts an entity relationship model’s entities, attributes, and relations.

· An entity is represented in the ERD by a rectangle, also known as an entity box. The name of the entity, a noun, is written in the center of the rectangle.

· The entity name is generally written in capital letters and in singular

form: SINGER rather than SINGERS, and SONG rather SONGS.

· Each row in the relational table is known as an entity instance or

entity occurrence in the ER model.

· A collection of similar entities is known as an entity set.

· Relationships describe associations among data.

· The type of relationship between entities

Attributes

· Each entity consists of a set of attributes that describes particular characteristics of the entity.

· In the original Chen notation, attributes are represented by ovals and are connected to the entity rectangle with a line. Each oval contains the name of the attribute it represents. The entity identifier is mapped as the table's primary key and is underlined.

Integrity Rules

· An entity integrity is the condition in which each row in a table has its own unique identity.

· A referential integrity is the conditional in which every reference to an entity instance by another entity instance is valid.

· Integrity rules are followed to maintain a good database design

Entity Integrity
Requirement -All primary key entries are unique, and no part of a primary key may be null.

Purpose- Each row will have a unique identity, and foreign key values can properly reference primary key values.

Example -No invoice can have a duplicate number, nor can it be null; in short, all invoices are uniquely identified by their invoice number.

Referential Integrity

Requirement-A foreign key may have either a null entry, as long as it is not a part of its table’s primary key, or an entry that matches the primary key value in a table to which it is related. Every non-null foreign key value must reference an existing primary key value.

Purpose-It is possible for an attribute not to have a corresponding value, but it will be impossible to have an invalid entry. The enforcement of the referential integrity rule makes it impossible to delete a row in one table whose primary key has mandatory matching foreign key values in another table.

Example-A customer might not yet have an assigned sales representative (number), but it will be impossible to have an invalid sales representative (number).