Database Languages: DDL, DML, and DCL

• Logical designing is the process of mapping an Entity Relationship Diagram (ERD) into relations.
• Logical database design involves creating a relational schema from an Entity Relationship Schema and validating the logical model.

Languages Used in Databases

• Data Definition Language (DDL) is used to define the structure of the database.
  • DDL functions include creating, altering, dropping, and truncating tables.
  • Altering changes the structure of the table.
  • Truncate removes all records from a table.
• Data Manipulation Language (DML) is used to change the data inside the database tables.
  • DML functions include selecting, inserting, deleting, and updating records.
  • Select retrieves table records.
  • Insert Into inserts values into a table.
  • Delete removes a row from a table.
  • Update makes changes in the records.
• Data Control Language (DCL) is used for granting and revoking permissions.
• Transaction Control Language (TCL) is used for saving (committing) and cancelling (rolling back) transactions.

Participation Constraints

• Constraints are limitations on database relationships.
• Total Participation: Each entity in an entity set must participate in a relationship.
  • If every employee must work for a department, then "Employee" has total participation in the "Works_For" relationship.
  • There cannot be be any employee if they are not assigned to a department.
• Partial Participation: Entities in an entity set may or may not participate in a relationship.
  • If a department can have employees, then "Department" has partial participation in the "Works_For" relationship.
  • This mean a department can exist in the database even if it had no employees.
• Participation constraints define whether the existence of an entity depends on its relationship with another entity.
• In an ER diagram, a project can be assigned a minimum of 3 and a maximum of 15 employees E (3,15).
• An employee can be assigned to a minimum of 0 and a maximum of 2 projects P(0,2)
• Minimum Cardinality illustrates whether the relationship is partial or total
  • Minimum Cardinality of 0 indicates Partial Participation.
  • Minimum Cardinality of 1 indicates Total Participation.

Mapping ERD

• The general format for mapping ERD is: Relation_Name (Primary_Key, Attribute_02, Attribute_03).
• The left of the bracket is the Relation Name and the entries in the brackets are the Field Names

Logical Design Algorithms

• The order to follow when doing a logical design:
  • Regular (Strong) Entity Types
  • Weak Entity Types
  • Binary 1:1 Relationship Types
  • Binary 1: N Relationship Types
  • Binary M:N Relationship Types
  • Multivalued Attributes

Mapping Entities

• An example is: Student (Roll_No, Name, Sex)
• A schema example with composite attributes: Student (Roll_no, First_name, Last_name, House_no, Street, City)

Mapping Multi-Valued Attributes

• A mobile_no can be a weak entity and weak relationship.
• To map a multi-valued attribute, create a new table.
• An example of a student mobile schema: Student_Mobile (Roll_No, Mobile_no)
• The primary key of a multivalued table is a combination of the owner entity's primary key and the multi-valued attribute.

Mapping Weak Entities

• The primary key of the weak entity table is a combination of the primary key(s) of the owner and the partial key of the weak entity.

Mapping Derived Attributes

• Derived attributes aren't normally shown when mapping ERDs because they are retrieving data.

Mapping 1:1 Relationship Types

• For a 1:1 binary relationship between entity types S and T, choose one as the "base" relation.
• If either S or T has total participation, choose that one as the base.
• Include the primary key of the other entity type as a foreign key in the base.
• Include any relationship attributes as attributes of the base.
• Forms of participation include: One Side, Both sides (total or partial)
• If Customer (Cus_ID, Cust_Name) and Loan (Loan_ID, Amount, Cus_ID) relate, the base entity is loan and the FK is customer relation.

Mapping 1:M Relationship Types

• These can be Total Participation - One Side, Total Participation - Both the sides, and Partial Participation - Both the sides

Mapping M:N Relationship Types

• These can be Total Participation - One Side, Total Participation - Both the sides, and Partial Participation - Both the sides

Mapping 1:1 Unary Relationship Types

• For a Person unary relationship, the format is: Person (nic, name, sNic), where 'nic' refers to the unique ID, name is the person's mane and sNic is the spouse's unique ID
• It is important to be clear on the attributes of this relationship

Mapping 1:M Unary Relationship Types

• Employee (eld, eName, mId), where eld is employee ID, eName is employee name, and mId is manager ID

Mapping M:N Unary Relationship Types

• e.g. With Item (itemNo, name, cost), Contains (itemNo, compoNo)

Steps for Mapping an ERD

• Follow this order:
  • Regular (Strong) Entity Types
  • Weak Entity Types
  • Binary 1:1 Relationship Types
  • Binary 1:N Relationship Types
  • Binary M:N Relationship Types
  • Mapping Multivalued Attributes