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.
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:
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.
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?
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:
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:
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?
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:
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.
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.
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)
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)
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)
Exercise 03 |
|---|
CREATE DATABASE GestElections;
USE GestElections;
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)
);
Liste Table:
CREATE TABLE Liste (
NumList INT PRIMARY KEY,
DesList VARCHAR(20) NOT NULL UNIQUE,
NbrCand TINYINT CHECK (NbrCand 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)
);
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)
);
ALTER TABLE Voter
ADD Datevote DATE DEFAULT CURRENT_DATE;
ALTER TABLE Candidat
MODIFY Appelation VARCHAR(30);
ALTER TABLE Electeur
DROP COLUMN NomPrenElv;
ALTER TABLE Electeur
ADD LastName VARCHAR(15) NOT NULL,
ADD FirstName VARCHAR(15) NOT NULL;
ALTER TABLE Electeur
ADD Gender ENUM('Boy', 'Girl') NOT NULL;
Query to compile vote statistics:
SELECT Gender, COUNT(*) AS VoteCount
FROM Electeur E
JOIN Voter V ON E.NumElv = V.NumElv
GROUP BY Gender;