Computer Science

to each answer?

i. σage>=23 AND address≠ kigali(“Customers”)

that represent relations.

relation is given in parenthesis following the σ.

Select Operation Notation Model

The result is a binary relation listing all the “Customers” with the name Mugisha.

result of σ name=’Mugisha’ (“Customers”)

         ii. σage>=23 AND address≠ kigali(“Customers”)

columns name and age

Project Operation Notation Model:

Eg:Example: Retrieve the suburbs that are stored in database

ii. π name,Salary(σSalary>5000) (“Customers”)

Intersection, Theta-join and Natural Join.

We have two relations, student and subject, as follows:

How can be generated the following table?

a scheme containing all fully qualified attribute names from R1 and R2R1.A1,…

product.

conceptual purpose.

Application activity 4.3:

Carry out the following activity:

Activity 4.4:

What was the operation performed in order to get this table?

R1UR2 to be legal,it is required that two conditions be held:

2. The domains of the ith attribute of R1 and ith attribute of R2 are the same.

Carry out the following activity:

From the following table:

What is the operation performed in order to generate the following table?

Consider relations R and S

cross product and theta join becomes clear.

the output would be

To join the tables use this symbol:

left side relation having null against the attributes of right side relation.

Consider the relation BOOK and relation STUDENT

Consider the Relation BOOK and Relation STUDENT

Consider the relation BOOK and relation STUDENT

values from another relation.

User

4. Consider ABC database containing the following relations:

a tutor having the same surname as the student.

xii. Delete from the table all employees in the IT department (code 14).

DDL: Data Definition Language

CREATE DATABASE XYZLtd; In MYSQL, it will look like the following:

If the RDBMS used is MYSQL, the result will look like this:

and start working with the School database.

o rows affected (0.48 sec)” is displayed. See below.

follows:

create table new_table_name as like existing_table_name [ where ]

Create table salary like “Customers”;

The structure of SALARY is displayed in the following interface.

The Basic syntax of DROP TABLE statement is as follows:

DESC “Customers”; and the answer will be like the following:

“CUSTOMERS” Relation

existing table.

        alter table table_name add column_name datatype;

               alter table table_name drop column column_name;

       alter table table_name modify column column_name datatype;

       alter table table_name modify column_name datatype not null;

   add constraint myuniqueconstraint unique(column1, column2...);

        add constraint myuniqueconstraint check (condition);

column2...);

alter table table_name drop constraint myuniqueconstraint;

alter table table_name drop index myuniqueconstraint;

alter table table_name drop constraint myprimarykey;

alter table table_name drop primary key;

• NOT NULL Constraint:

NULLs:

);

would be set to 5000.

SALARY column, write a statement similar to the following:

To drop a DEFAULT constraint, use the following SQL:

records with same age:

AGE column, you would write a statement similar to the following

multiple columns as well:

To drop a UNIQUE constraint, use the following SQL:

If you are using MySQL, then you can use the following syntax

The syntax to define ID attribute as a primary key in a “CUSTOMERS” table is:

syntax:

“CUSTOMERS” table already exists, use the following SQL syntax:

, use the Syntax:

Primary Key in a different table.

“CUSTOMERS” table:

ORDERS table:

the syntax for specifying a foreign key by altering a table.

To drop a FOREIGN KEY constraint, use the following SQL:

Customer below 18 years:

column, you would write a statement similar to the following:

multiple columns as well:

MySQL:

use the following command:

following command:

constraint, and then enable it later.

follows:

ways to insert records (tuples) in a table:

First method:

Second method:

All the above statements would produce the following records in “Customers” table:

SELECT Syntax

from. If you want to select all the fields available in the table, use the following syntax:

SELECT * FROM “Customers”;

used to return only distinct (different) values.

SELECT DISTINCT Syntax

operator displays a record if the condition(s) is NOT TRUE.

“Huye” OR “Nyamagabe”;

“Nyamagabe”:

SELECT * FROM “Customers” WHERE NOT Address=’Nyamagabe’;

Address=’Nyamagabe’);

SELECT * FROM “Customers” WHERE NOT Address=”Nyamagabe” AND NOT Address=”GASABO”;

x. Delete books written by “Kagame”.

6. SQL COUNT Function

of records, which are expected to be returned by a SELECT statement.

be done as follows to count records whose salary is 2000:

record set.

set.

well.

SQL AVG function is used to find out the average of a field in various records.

SQL SUM function is used to find out the sum of a field in various records.

vi. Find the average price of the books

details the important string functions:

avoid that, you can use an explicit type cast, as in this example:

              b. LEFT(str,len)

NULL.

LENGTH returns 10, whereas CHAR_LENGTH returns 5.

current character set mapping.

Returns the string str with the order of the characters reversed.

Consider “Customers” and orders tables as follows:

These two tables (relations) have the following records:

Now, let us join these two tables in our SELECT statement as follows:

This would produce the following result:

symbol.

more joined tables.

of rows of A and B are combined into a result row.

The basic syntax of INNER JOIN is as follows:

values from the right table or NULL in case of no matching join predicate.

The basic syntax of LEFT JOIN is as follows:

The basic syntax of RIGHT JOIN is as follows:

The basic syntax of FULL JOIN is as follows:

then you can use UNION ALL clause to combine two JOINS as follows:

The basic syntax of SELF JOIN is as follows:

The basic syntax of INNER JOIN is as follows:

The basic syntax of UNION is as follows:

ALL operator.

The basic syntax of UNION ALL is as follows:

The basic syntax of INTERSECT is as follows:

Example:

The basic syntax of EXCEPT is as follows:

Example:

alias.

The basic syntax of table alias is as follows:

The basic syntax of column alias is as follows:

Following is the usage of column alias:

The basic syntax of TRUNCATE TABLE is as follows:

Example:

The following is the position of the HAVING clause in a query:

statement, including the HAVING clause:

would be more than or equal to 2:

Subqueries are most frequently used with the SELECT statement. The basic syntax

is as follows:

the subquery can be modified with any of the character, date or number functions.

The basic syntax is as follows:

syntax:

UPDATE statement.

The basic syntax is as follows:

“Customers” whose AGE is greater than or equal to 27:

other statements mentioned above.

The basic syntax is as follows:

whose AGE is greater than or equal to 27:

The basic syntax of DELETE query with WHERE clause is as follows:

Following is an example, which would DELETE a customer, whose ID is 6:

use WHERE clause and DELETE query would be as follows:

a database object.

The Syntax for the GRANT command is:

employee from user1, still user2 will have SELECT privilege on employee table.

The Syntax for the REVOKE command is:

revokes it. You cannot REVOKE privileges if they were not initially granted by you.

apply.

iv. Revoke selection rights to students

how. If no, explain

of security.

Data integrity refers to the overall completeness, accuracy and consistency of data.

and even from user to user.

which should be restricted.

• Protection of privacy.

security issues.

reported in the media today involve major losses of confidentiality.

operations may produce an incorrect result.

iii. Can database be attacked by hackers?

security goals: integrity, availability, and confidentiality.

users, physical damage, and Data corruption.

The protection of a database can be done through access control and data encryption

jobs.

and the protection of the database against the potential threats.

Those steps are:

• Operational maintenance

A. Database Planning

for it all.

database system. Described in the requirements specification.

for a database system with multiple user views.

design stage.

database.

◊ Bottom-up or Normalisation

* Suitable for complex databases.

* Suitable for small number of attributes.

relationships, and attributes.

* Tailored to a specific DBMS.

products and Recommend selection and produce report.

aircraft.

the courses that they teach is less than five.

courses & books adopted for each course.

vii. Create suitable front end for querying and displaying the results.

vii. Create suitable front end for querying and displaying the results.

with your back end (database).

keep your database secure.