SEC S20W1 || Databases and SQL language - Part 1

Hello everyone! I hope you will be good. Today I am here to participate in the contest of Dynamic Devs about the Databases and SQL language. It is really an interesting and knowledgeable contest. There is a lot to explore. If you want to join then:

---

---

Join Here: SEC S20W1 || Databases and SQL language - Part 1

---

---

Designed with ^Canva

Exercise 1

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

Here are the validations and answers for each proposition:

1. A column corresponds to:

   • an index: F
   • an elementary property of a real-world object: V
   • a real-world object: F

2. 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

3. 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

4. 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

5. Integrity constraints on a column are:

   • PRIMARY KEY: V
   • CHECK: V
   • FOREIGN KEY: V
   • REFERENCES: V

6. 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

7. 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

---

Exercise 2

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

The above table has been copied from the professor's post.

Identifying the anomalies in the original table structure:

The provided table by the professor suffers from a number of anomalies. It has anomalies related to data redundancy, update anomalies as well as insertion and deletion anomalies. These are given below:

1. Redundancy: The title of the function intfctand the function codfct are repeated for the employees. For Example: Finance is listed 2 times for the employees E001 and E003 which causes redundancy in the data.

2. Update anomalies: Similarly if we need to update the title of a function or the code then we have to make the changes in the multiple rows. And it increases the chances of the inconsistency in the data. For example: If we want to rename the Finance to something else then we need to make changes on multiple rows wherever codfct = 20 appears.

3. Insertion anomaly: If we create a new function without assigning any employees to it then there is no place in this table to store that function without creating a dummy employee.

4. Deletion anomaly: If we remove an employee from the table who is the only one in a specific function then the information about that function will also be deleted which is undesirable.

Textual representation of the database after applying the management rules R1 and R2:

Based on the provided rules R1 and R2 we can correct the above anomalies by normalizing the table into multiple tables. Here is the textual representation of the of the database after applying the management rules R1 and R2:

We can create 3 tables Employee, Function, and Employee_Function.

The details of the table Employee are given below:

Table: Employee

• codemp (Primary Key): Employee code
• nomemp: Employee's last name
• prenemp: Employee's first name
• dnemp: Employee's date of birth
• niveau: Employee's recruitment level (Master, Technician, High School)

---

The details of the table Function are given below:

Table: Function

• codfct (Primary Key): Function code
• intfct: Function title ( Administrative, Finance, Security, and Legal )

---

The details of the table Employee_Function are given below:

Table: Employee_Function

• codemp (Foreign Key, Composite Primary Key): Employee code
• codfct (Foreign Key, Composite Primary Key): Function code

---

So these are the 3 new tables from the single table with anomalies.

Explanation

• The Employee table stores the information about the employees. But in this new table the function data does not repeat.

• The Function table stores the details of each functions. It stores the details including title and code only once without repeating the data.

• The Employee_ Function table is used to relate the employees to their respective functions. It ensures that each employee is assigned to only one function as per the new rule of R1.

---

R1 becomes: An employee can hold one or more functions, and for each assignment, the start and end dates of the assignment are recorded.

Textual representation of the database after modifying R1

By taking into account the requirement where one employee can hold one or more functions and for each assignment the start and the end dates of the assignment are recorded we need to adjust the database structure to include the start and end dates for each function assignment.

We need to add a new table Employee_Function_Assignment which will record the assignment of function for each employee. So the textual representation of new structure of the database is given below:

Table: Employee

• codemp (Primary Key): Employee code
• nomemp: Employee's last name
• prenemp: Employee's first name
• dnemp: Employee's date of birth
• niveau: Employee's recruitment

Table: Function

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

Table: Employee_Function_Assignment

• codemp (Foreign Key, Composite Primary Key): Employee code
• codfct (Foreign Key, Composite Primary Key): Function code
• start_date (Composite Primary Key): Start date of the assignment
• end_date: End date of the assignment (can be NULL for current assignments)

Explanation

• The new table Employee_Function_assignment table now records the assignment of function for each employee. It records the assignment dates from start to end. It allows multiple assignments over time while keeping the record of function changes.

---

Textual representation of the "Bonus" table (new rule R3)

According to the requirement to handle the performance bonus for the employees we need to add a new table in the database. The details of this tables are given below in textual representation:

Table: Bonus

• codemp (Foreign Key, Composite Primary Key): Employee code
• AnneePrime (Composite Primary Key): bonus year
• PrimeRend: Value of the performance bonus

Explanation

The bonus table ensures that an employee can get multiple performance bonuses in same year. The composite primary key codemp, AnnePrime ensures that multiple records of bonus can exist for the same employee in a given year.

---

---

Exercise 3

Here are the steps to create the database for the elections.

1. Create the "GestElections" database

CREATE DATABASE GestElections;
USE GestElections;

The above code creates a Database with the name GestElections and I have selected it for the use.

2. Create the various tables in the "GestElections" database

Here I am creating a table Electeur.

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)
);

Above is the sql code from which I have created a table Electeur in the database DATABASE which I created recently. This table stores the information about the electors. It saves inforation by including their ID, first name, last name, and age. In this table NumElv field is auto-incremented and it is serving as the primary key.

Here I am creating a table Liste


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


This table Liste stores the information about candidate lists, list number, description and the number of candidates. There is a restriction on the number of candidates that this range should be in between 1 and 6.

Now I am creating a table Candidat.

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

The above code generates the table Candidate to store the information of the candidates. It stores the candidate's ID, list number, and designation. Designation is represented by Appelation. Similarly Numlist field references the Liste table. In this table NumCand is serving as the primary key.

Now I am going to create a table Voter

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)
);


The above code creates the Voter table. This table stores the voting information. It links electors (NumElv), candidates (NumCand) and the lists (NumList). This table also has Datevote field which will be used to track the voting date.

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

ALTER TABLE Voter
ADD Datevote DATE DEFAULT CURRENT_DATE;


In the above code I have added a Datevote column to the Voter table using ALTER command and then for the addition of the column I have used ADD command. By default it will carry the current date whenever a new record is inserted.

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

ALTER TABLE Candidat
MODIFY Appelation VARCHAR(30);


In order to expand the Appelattion column of the Candidate table up to 30 characters from 20 characters I have used ALTER command and then MODIFY command to update its value. Now it will hold 30 characters rather than 20 characters.

5. 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;

In order to split the NomPrenElv column of the Electeur table into LastName and FirstName I have removed the previous column NomPrenElv from the Electeur table. Then I have added 2 new columns LastName and FirstName in the Electeur table. I have restricted each column to contains 15 characters. And I have set both the fields NOT NULL making them mandatory.

6. Proposal for tracking votes by gender

It is simple but a little bit critical to track the votes by gender because Electeur table does not has any gender column. So in order to resolve this issue and to track the votes based on genders I can propose adding a new Column of Gender.

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

Here I have added a new column Gender using the ENUM property. And I have made this field mandatory by setting it NOT NULL.

Here is an example query to compile vote statistics based on the gender:

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

This query generates the statistics by counting the number of votes per gender by joining the Electeur and Voter tables and then grouping the results by the Gender column.

---

Conclusion

Database and sql is an important language and it is the backbone of many things. It is used to manage and organize the data efficiently. We store all the information and data in the database. Normalization process helps us to eliminate the redundancy in the data. The concept of normalization ensures that the data is accurate and scalable.