SEC S20W1 || Databases and SQL language - Part 1

Good night friends, today is Friday 29th Bhadra 1431 A.D., 13th September 2024 A.D., 8th Rabiul Awal 1446 A.H., how are you all, hope everyone is well and having a very nice day. I want to thank @kouba01 to organize this course in SEC season 20 which is named "Databases and SQL language" and today i am participating on the first part. I would also like to invite @sifat420, @yoyopk and @baizid123 to participate in this post.

Image designed in canva

Exercise 01

For each of the following statements, validate each proposition by putting the letter V if it is correct or F if it is incorrect.

A column corresponds to:

• an index → (F)
• an elementary property of a real-world object → (V)
• a real-world object → (F)

Justification: A column maps to an element property of a real-world object as it is the basic vital thing or attribute of that object. This is also an approach to reduce the complexity of real-time objects into a simple and organized data.

---

Mark the letter V in front of the correct word to complete the following sentence:
A ……….. is a set of data related to the same subject (or entity) and structured in a table format.

• Database → (F)
• Table → (V)
• Row → (F)

Justification: Table is a collection of data that fall under the same subject (or entity), formatted as a table due to table being how we logically organise and display information.

---

Among the following propositions, which correspond to a primary key?

• A field that does not accept duplicates → (V)
• Allows identifying information to retrieve it → (V)
• First record in a table → (F)

Justification: A primary key must be unique for each record, ensuring no duplicates and its main role is to uniquely identify and retrieve a specific record. The third one doesn't relate to a primary key, as the primary key applies to all records, not just the first one.

---

In SQL, to ensure data integrity in a child table after modifying the primary key value of a parent table, we use the clause:

• CHECK → (F)
• ON UPDATE CASCADE → (V)
• ON DELETE CASCADE → (F)

Justification: When a parent table's primary key values change, the foreign key values in the child table will automatically update to reflect those changes while maintaining referential integrity which is known as ON UPDATE CASCADE.

---

Integrity constraints on a column are:

• PRIMARY KEY → (V)
• CHECK → (V)
• FOREIGN KEY → (V)
• REFERENCES → (V)

Justification: PRIMARY KEY ensures unique, non-null values. CHECK applies the specified conditions to the column values. FOREIGN KEY ensures values correspond to values in another table. REFERENCES is used with FOREIGN KEY to specify the linked table and column.

---

In SQL, which clauses placed after the data type of a field ensure that the content of this field is unique?

• PRIMARY KEY → (V)
• UNIQUE → (V)
• NOT NULL → (F)
• CONSTRAINT → (F)

Justification: PRIMARY KEY ensures uniqueness and non-null values. UNIQUE ensures all values are unique but allows one null (if supported).

---

In SQL, the Drop Table command allows:

• Deleting integrity constraints on a table only → (F)
• Deleting a table → (V)
• Deleting one or more rows from a table → (F)
• Deleting one or more columns from a table → (F)

Justification: DROP TABLE removes the full table, including its data, structure, and constraints, from the database and does not just remove only rows or columns.

---

Exercise 02

To describe the employees of a company and their distribution among different functions, the following table has been created:

codemp	nomemp	prenemp	dnemp	niveau	codfct	intfct
E001	TOUNSI	Safa	1980-01-10	Master	20	Finance
E002	KEFI	Ali	1981-09-12	High School	10	Administrative
E003	BEJI	Mohamed	1977-04-15	High School	20	Finance
E004	TOUATI	Lamia	1965-06-21	Technician	20	Security
E005	SOUSSI	Leila	1982-11-28	Master	10	Administrative
E006	SFAXI	Lotfi	1988-06-09	Master	30	Legal

Description of Columns

Column	Description	Column	Description
codemp	Employee code	dnemp	Employee's date of birth
nomemp	Employee's last name	niveau	Recruitment level
prenemp	Employee's first name	codfct	Function code
intfct	Function title

This table has been copied from the professor @kouba01's post.

---

Anomalies in the Current Structure:

The table provided by the professor to us has various anomalies like data redundancy, update anomalies as well as insertion and deletion anomalies. These anomalies are given below:

Redundancy Anomalies: The fields intfct (function title) and codfct (function code) are repeated for every employee in the same function. For example, employees in the "Finance" function have the same values for both intfct and codfct. This redundancy leads to unnecessary data duplication.

Update Anomaly: If the function title or code changes (e.g., renaming "Finance" to "Accounting"), we would need to update multiple rows where the function is assigned. This could lead to inconsistent data if one row is updated but others are missed.

Insert Anomaly: If we want to add a new function but there are no employees yet, we cannot insert the function details (function title and code) because there would be no employee record to associate it with.

Delete Anomaly: If the last employee in a function leaves the company and their record is deleted, the information about that function (its title and code) would be lost since it's tied to the employee records.

---

Textual representation of this database with R1 and R2 applied

The provided anomalies in various tables will be normalized in accordance with R1 and R2 criteria. Following the application of R1 and R2 management rules, the texture representation in various tables is as follows.

Table: Employee
codemp: Employee code (Primary Key)
nomemp: Employee's last name
prenom: Employee's first name
demp: Employee's date of birth
niveau: Recruitment level (Foreign Key)

Table: Function
codfct: Function code (Primary Key)
intfct: Function title

Table: Employee_Function
codemp: Employee code (Foreign Key)(Primary Key)
codfct: Function code (Foreign Key)(Primary Key)

Explanation:

• Stores employee information.
• Function data does not repeat in this table.
• Stores function details (title and code).
• Each function's data is stored only once, preventing repetition.
• Relates employees to their respective functions.
• Ensures that each employee is assigned to only one function, following Rule R1.

---

Textual representation of this database with R1 and R2 applied

Table: Employee
codemp: Employee code (Primary Key)
nomemp: Employee's last name
prenom: Employee's first name
demp: Employee's date of birth
niveau: Recruitment level (Foreign Key)

Table: Function
codfct: Function code (Primary Key)
intfct: Function title

Table: Employee_Function
codemp: Employee code (Foreign Key)(Primary Key)
codfct: Function code (Foreign key)(Primary Key)
start_date: Start date of the assignment(Primary Key)
end_date: End date of the assignment (It can be null)

Explanation:

• Records the assignment of functions for each employee.
• Includes the start and end dates for each assignment.
• Allows multiple function assignments for an employee over time.
• Keeps a history of function changes by tracking the dates of each assignment.

---

Textual representation of this database with R1 and R2 applied

Table: Bonus
codemp: Employee code (Foreign Key)(Primary Key)
anneePrime: Year of the performance bonus(Primary Key)
primeRend: Value of the performance bonus(Primary Key)

Explanation:

• Allows employees to receive multiple performance bonuses in the same year.
• Consists of codemp (Employee code) and AnnePrime (Year).
• Ensures multiple bonus records can exist for the same employee within a given year, maintaining uniqueness for each bonus entry.

---

Exercise 03

Create the "GestElections" database

CREATE DATABASE GestElections;
USE GestElections;

Explanation:

• The CREATE DATABASE command creates a database named GestElections.
• The database is now available and ready to be used.
• WE need to SELECT or USE this database to create tables and perform operations within it.

---

Create the various tables in the "GestElections" database

Electeur Table:

CREATE TABLE Electeur (
NumElv INT AUTO_INCREMENT PRIMARY KEY,
LastName VARCHAR(15) NOT NULL,
FirstName VARCHAR(15) NOT NULL,
AgeElv TINYINT CHECK (AgeElv BETWEEN 12 AND 20)
);

Explanation:

• Created a database named GestElections.
• Created a table named Electeur in the GestElections database.
• NumElv: Auto-incremented field serving as the primary key (unique ID for each elector).
• First Name: Stores the first name of the elector.
• Last Name: Stores the last name of the elector.
• Age: Stores the age of the elector.
• The Electeur table stores and manages information about electors.

---

Liste Table:


CREATE TABLE Liste (
NumList INT PRIMARY KEY,
DesList VARCHAR(20) NOT NULL UNIQUE,
NbrCand TINYINT CHECK (NbrCand BETWEEN 1 AND 6)
);


Explanation:

• List Number (NumList): Unique identifier for each candidate list.
• Description (DesList): Description or name of the list.
• Number of Candidates (NbrCand): The number of candidates in the list, with a restriction to be between 1 and 6.

---

Candidate Table:

CREATE TABLE Candidat (
NumCand INT PRIMARY KEY,
NumList INT NOT NULL,
Appelation VARCHAR(30) NOT NULL,
FOREIGN KEY (NumList) REFERENCES Liste(NumList)
);

Explanation:

• Candidate ID (NumCand): Unique identifier for each candidate, serving as the primary key.
• List Number (NumList): References the Liste table, linking the candidate to a specific list.
• Designation (Appelation): Represents the candidate's designation or title.
• NumCand is used as the primary key to uniquely identify each candidate.
• NumList references the Liste table to associate each candidate with a particular list.

---

Voter Table:

CREATE TABLE Voter (
NumElv INT,
NumCand INT,
NumList INT,
Datevote DATE,
PRIMARY KEY (NumElv, NumCand, NumList),
FOREIGN KEY (NumElv) REFERENCES Electeur(NumElv),
FOREIGN KEY (NumCand) REFERENCES Candidat(NumCand),
FOREIGN KEY (NumList) REFERENCES Liste(NumList)
);


Explanation:

• Elector ID (NumElv): Links to the Electeur table, identifying the elector who voted.
• Candidate ID (NumCand): Links to the Candidate table, identifying the candidate who received the vote.
• List Number (NumList): Links to the Liste table, identifying the list associated with the vote.
• Voting Date (DateVote): Records the date on which the vote was cast.

---

Add a "Datevote" column representing the current date to the "Voter" table

ALTER TABLE Voter
ADD Datevote DATE DEFAULT CURRENT_DATE;


Explanation:

• Added a DateVote column to the Voter table using the ALTER command.
• Used the ADD command to include the DateVote column.
• By default, the DateVote column is set to the current date for each new record inserted.

---

Expand the "Appelation" column of the "Candidat" table to 30 characters

ALTER TABLE Candidat
MODIFY Appelation VARCHAR(30);


Explanation:

• Expanded the Appelation column in the Candidate table from 20 characters to 30 characters.
• Used the ALTER command to modify the table structure.
• Applied the MODIFY command to update the column size.
• Applied the MODIFY command to update the column size.

---

Split the "NomPrenElv" column of the "Electeur" table into "LastName" and "FirstName"

ALTER TABLE Electeur
DROP COLUMN NomPrenElv;

ALTER TABLE Electeur
ADD LastName VARCHAR(15) NOT NULL,
ADD FirstName VARCHAR(15) NOT NULL;

Explanation:

• Removed the NomPrenElv column from the Electeur table.
• Added two new columns: LastName and FirstName.
• Both LastName and FirstName are restricted to 15 characters each.
• Set both columns to NOT NULL, making them mandatory.

---

Create the "GestElections" database

ALTER TABLE Electeur
ADD Gender ENUM('Boy', 'Girl') NOT NULL;

Explanation:

• Added a Gender column using the ENUM data type.
• The Gender column is set to NOT NULL, making it a mandatory field.

---

Query to compile vote statistics:

SELECT Gender, COUNT(*) AS VoteCount
FROM Electeur E
JOIN Voter V ON E.NumElv = V.NumElv
GROUP BY Gender;

Explanation:

• Generates statistics on the number of votes per gender.
• Joins the Electeur and Voter tables.
• Groups the results by the Gender column.
• Counts the number of votes for each gender.