SEC S20W1 || Databases and SQL language - Part 1

Greetings Steemit friends

• 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. A column corresponds to:

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

---

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 (V)

---

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 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 (V)

---

Integrity constraints on a column are:

PRIMARY KEY (V)
CHECK (V)
FOREIGN KEY (F)
REFERENCES (V)

---

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 (V)

---

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 02

From the table, I notice several employees having the same recruitment level. This can be seen as a redundancy of data, which is stressful to input the same data every time a new employee need to be added to the system. One-to-many relationships will help fix the anomalies in the data collected. We try to ensure that each table holds data related to a single entity. So we will create a table for employees to put their personal information. Create a table Employee_function to hold all available functions in the company with their respective codes. Next, create Recruitment_level table to hold the different Recruitment level level.

Question 2a: Provide a textual representation of this database.

Table: Employee

codemp Employee code (Primary Key)
nomemp Employee's last name
prenom Employee's first name
demp Employee's date of birth
function_recruit_id Function_Recruitment ID (Foreign Key referencing Function_recruitment)

Table: Employee_function

function_id: Function ID(Primary Key)
intfct Function title
codfct Function code

Table: Recruitment_level

recruit_id Recruitment ID(Primary Key)
niveau Recruitment level

Table: Function_recruitment

function_recruit_id Primary Key
function_id Function ID (Foreign Key referencing Employee_function)
recruit_id Recruitment ID (Foreign Key referencing Recruitment_level)

The purpose of the Function_recruitment table is to be able to assign more than one function to an employee,

Question 2b: Provide a textual representation of this database.

Table: Employee

codemp Employee code (Primary Key)
nomemp Employee's last name
prenom Employee's first name
demp Employee's date of birth

Table: Employee_function

function_id: Function ID(Primary Key)
intfct Function title
codfct Function code

Table: Recruitment_level

recruit_id Recruitment ID(Primary Key)
niveau Recruitment level

Table: Function_recruitment

idfunction_recruit Primary Key,
function_id Function ID (Foreign Key Table Employee_function)
recruit_id Recruitment ID (Foreign Key Table Recruitment_level)
codemp Employee code (Foreign Key Table Employee)
starte_date Assign Function Date Start,
end_date Assign Function Date End,

Now we have an update on the Employee and Function_recruitment tables to assign different functions at different times to a single employee. Note that I have changed the relationship between these two tables.

Question 2c: Provide a textual representation of this database.

Table: Bonus

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

Exercise 03

Create the "GestElections" database:

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

Create table Electeur

Create table Liste

Create table Candidat

Create table Voter

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

Final Results

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

The entity gender can only be assigned to the elector. We can only have an elector, a boy or a girl. So. it will be proper to add a column to the Electeur table.

Compile statistics on votes according to gender.

Using the CASE statement in each COUNT will compile only the statistics of the respective gender. We have zero in both cases, because our table does not hold any entry for now.

---

---

Cheers
Thanks for dropping by
@fombae