Dear Professor,
This is my submission for the Engagement Challenge. I am impressed with this time homework contest relate with Databases and SQL Language. I want to Thank you to professor @kouba01 to organizing such a valuable contest.
For each of the following statements, validate each proposition by putting the letter V if it is correct or F if it is incorrect.
Q1) A column corresponds to:
• an index: F
• an elementary property of a real-world
object: V
• a real-world object: F
Instead of being a simple property, a column in MySQL usually reflects an attribute of an actual object, such its name or age.
Mark the letter V in front of the correct word to complete the following sentence:
Q2) 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
A table makes comparison and analysis easier by arranging data into rows and columns and clearly showing relevant information.
Q3) 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
In database parlance, this type of field is called a "primary key" or "unique identifier". It guarantees that every record is unique and that it may be effectively accessed or retrieved by utilizing its unique value.
Q4) 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
By automatically updating foreign key values in the child table to correspond with modifications in the parent table's primary key and maintaining referential consistency, the ON UPDATE CASCADE clause guarantees data integrity.
Q5) Integrity constraints on a column are:
• PRIMARY KEY: V
• CHECK: V
• FOREIGN KEY: V
• REFERENCES: V
Data accuracy and consistency are guaranteed by integrity constraints such as PRIMARY KEY, CHECK, FOREIGN KEY, and REFERENCES. The primary key ensures unique identity, the foreign key preserves relational integrity, the referential linkages between tables are enforced by REFERENCES, and values are validated by CHECK.
Q6) 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
Both UNIQUE and PRIMARY KEY guarantee uniqueness; UNIQUE permits more than one null value, whereas PRIMARY KEY only permits unique, non-null values.
Q7) 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
A table and its contents are permanently removed from the database by using the DROP TABLE command, which also frees up resources and space.
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 suffers from anomalies from its creation. Identify the anomalies in this structure and explain their origins.
Identifying Redundancy and Update Anomalies: The present table structure contains both function-related information (function code and title) and recruitment-level information, which may result in redundancy and update anomalies.For example, if a function title changes, it must be changed in all records where that function appears.
Insertion Errors: Adding a new function or recruitment level necessitates inserting numerous records. For example, if a new function is added, you must insert numerous entries, which may result in inconsistency if the function details are not updated consistently.
To correct the anomalies, we propose the following management rules:
R1: An employee is assigned to only one function, and a function can include several employees.
R2: An employee has only one recruitment level, and a recruitment level concerns several employees.
Question: Provide a textual representation of this database.
For the DBMS to take into account that an employee can occupy a position in the company for a determined period, and may occupy the same position at different times,
R1 becomes: An employee can hold one or more functions, and for each assignment, the start and end dates of the assignment are recorded.
Question: Provide the textual representation of this database.
We propose to add a table named "bonus" to record performance bonuses awarded to each employee during a year, with the fields AnneePrime as the year of the bonus and PrimeRend as the value of the bonus. To do this, we must respect the new management rule R3: An employee can receive performance bonuses during the same year.
Question: Provide the textual representation for this table.
Here is my answer for the Exercise 2,
Function Information: For every employee designated to a certain function, the intfct (Function title) is repeated. There may be discrepancies if a function's title changes because all records containing that function will need to be changed. Information about the Recruitment Level: Each employee receives the same niveau (Recruitment level), which could lead to redundancy if the description of the Recruitment Level changes.
Insertion Anomalies:
It may be inefficient and error-prone to ensure that all relevant personnel records are updated when new functions or recruitment levels are added.
Anomalies of Deletion: If there is no other record connecting the removal of a function or recruitment level to its specifics, information may be lost. Modified Database Structure First Textual Illustration:
Employee Table:
codemp: Employee code (Primary Key)
nomemp: Employee's last name
prenemp: Employee's first name
dnemp: Employee's date of birth
codfct: Function code (Primary Key)
intfct: Function title
Recruitment Level Table:
niveau: Recruitment level (Primary Key)
Employee Function Assignment Table:
codemp: Employee code (Foreign Key from Employee Table)
codfct: Function code (Foreign Key from Function Table)
start_date: Start date of the function assignment
end_date: End date of the function assignment (can be NULL if currently assigned)
Primary Key: (codemp, codfct, start_date)
With New Requirements for Multiple
codemp: Employee code (Primary Key)
nomemp: Employee's last name
prenemp: Employee's first name
dnemp: Employee's date of birth
Function Table:
codfct: Function code (Primary Key)
intfct: Function title
niveau: Recruitment level (Primary Key)
Employee Function Assignment Table:
codemp: Employee code (Foreign Key from Employee Table)
codfct*: Function code (Foreign Key from Function Table)
*start_date: Start date of the function assignment
end_date: End date of the function assignment (can be NULL if currently assigned)
Primary Key: (codemp, codfct, start_date)
codemp: Employee code (Foreign Key from Employee Table)
AnneePrime: Year of the bonus
PrimeRend: Value of the bonus
Primary Key: (codemp, AnneePrime)
This format eliminates redundancy, sets off employee data from function and recruiting level specifics, and facilitates the effective tracking of numerous assignments and performance bonuses.
Let’s consider the database "GestElections," which contains information about the elections of the foundation council members of a high school. It is described by the following simplified textual representation:
| Electeur (NumElv, NomPrenElv, AgeElv) |
| Candidat (NumCand, NumList#, Appelation) |
| Liste (NumList, DesList, NbrCand) |
| Voter (NumCand#, NumList#, NumElv#) |
Description of the columns in the tables
| Column | Description | Data Type | Size | Mandatory | Table |
|---|---|---|---|---|---|
| NumElv | Number of an elector student | Auto-increment | 3 | Yes | Electeur |
| NomPrenElv | Elector student's full name | Text | 20 | Yes | Electeur |
| AgeElv | Elector student’s age [12..20] | Numeric | 2 | No | Electeur |
| NumCand | Number of a candidate student | Numeric | 2 | Yes | Candidat |
| NumList | List number | Numeric | 1 | Yes | Liste |
| Appelation | Candidate's designation | Text | 20 | Yes | Candidat |
| DesList | Unique designation of a list | Text | 20 | Yes | Liste |
| NbrCand | Number of candidates [1..6] | Numeric | 2 | No | Liste |
The database administrator asks you to help create and modify the structure of the tables indicated above.
Therefore, provide the SQL commands to:
• Create the "GestElections" database:
• Create the various tables in the "GestElections" database.
• Add a "Datevote" column representing the current date to the "Voter" table.
• Expand the "Appelation" column of the "Candidat" table to 30 characters.
• Split the "NomPrenElv" column of the "Electeur" table into a "LastName" and "FirstName," each of 15 characters (both fields are mandatory).
The head of the high school wants to compile statistics on votes according to the gender of the elector (boy or girl) but cannot find results.
What can be proposed to him?
Write your proposal in SQL.
Here's a list of SQL commands to complete the requested tasks:
CREATE TABLE Electeur (
NumElv INT AUTO_INCREMENT PRIMARY KEY,
LastName VARCHAR(15) NOT NULL,
FirstName VARCHAR(15) NOT NULL,
AgeElv INT
);
CREATE TABLE Candidat (
NumCand INT PRIMARY KEY,
NumList INT NOT NULL,
Appelation VARCHAR(20) NOT NULL,
FOREIGN KEY (NumList) REFERENCES Liste(NumList)
);
CREATE TABLE Liste (
NumList INT PRIMARY KEY,
DesList VARCHAR(20) NOT NULL,
NbrCand INT
);
CREATE TABLE Voter (
NumCand INT,
NumList INT,
NumElv INT,
Datevote DATE,
PRIMARY KEY (NumCand, NumList, NumElv),
FOREIGN KEY (NumCand) REFERENCES Candidat(NumCand),
FOREIGN KEY (NumList) REFERENCES Liste(NumList),
FOREIGN KEY (NumElv) REFERENCES Electeur(NumElv)
);
SQL code:-
ALTER TABLE Candidat
MODIFY Appelation VARCHAR(30) NOT NULL;
SQL code:-
Assuming you have a copy of the data on hand or that dropping and rebuilding the table is OK.Divide the "NomPrenElv" Column into "LastName" and "FirstName" in the "Electeur" Table.
-- Drop existing table
DROP TABLE Electeur;
-- Recreate table with split columns
CREATE TABLE Electeur (
NumElv INT AUTO_INCREMENT PRIMARY KEY,
LastName VARCHAR(15) NOT NULL,
FirstName VARCHAR(15) NOT NULL,
AgeElv INT
);
--If you have a technique for splitting names, you can replicate old data into the new form. Proposal for Gender-based Statistics To collect information based on the gender of the elector (boy or girl), first add a gender column to the Electeur table. Here's how to change the schema and input sample data to allow gender-based queries:
Add Gender Column to Electeur Table:
sql code
ALTER TABLE Electeur
ADD Gender CHAR(1) CHECK (Gender IN ('M', 'F')) NOT NULL;
Update the Electeur Table with Gender Data:
sql code:-
-- Example: Update gender data for existing records
UPDATE Electeur
SET Gender = 'M'
WHERE LastName = 'Smith' AND FirstName = 'John'; -- Use appropriate conditions
Compile Statistics Based on Gender:
sql code:-
-- Count the number of voters by gender
*SELECT Gender, COUNT() AS **
NumberOfVoters
FROM Electeur
JOIN Voter ON Electeur.NumElv = Voter.NumElv
GROUP BY Gender;
By adding a gender column and adjusting the queries, you can easily compile statistics and analyze voting patterns based on gender.
Thank you for giving a time!