Scribd
Upload
29 views8 pages

SQL Queries and ERD for Database Concepts

Uploaded by

mohamed9ahmed12
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
29 views8 pages

SQL Queries and ERD for Database Concepts

Uploaded by

mohamed9ahmed12
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Ministry of Communications and information Technology

Information Technology Institute

Database Fundamentals
Intake : 29 Time allowed : 2.5 hours
Name: Group:

Question1

Part 1: Answer with Yes or No.


1. In a nested query, the inner query executes first, and once, before the outer query
2. There are physical records corresponding to user created views.
3. Relational data base relation may accept multi-valued data items (attributes).
4. Deleting all tuples of a table is equivalent to dropping it.
5. In a table the records are ordered top to bottom.

Part 2: Choose the correct answer


1. Which of the following is NOT an advantage of enforcing integrity constraints in
the database management system:
A) Keep the integrity between tables
B) Updating constraints will be easier.
C) Maintain consistency among rows in relations.
D) None
2. Consider the following two tables, called X and Y:

Which of the following queries will return the greatest number of rows?

A) SELECT * FROM X, Y
B) SELECT * FROM X, Y WHERE [Link] > [Link]
C) SELECT * FROM X
UNION
SELECT * FROM Y
D) SELECT * FROM X X1, X X2 WHERE [Link] = [Link]

3. Consider the relational schema R(A, B, C, D, E) with non-key functional


Dependencies
(C, DE)

1
Ministry of Communications and information Technology
Information Technology Institute

(BC)
Select the strongest statement that can be made about the schema R
A) R is in first normal form
B) R is in second normal form
C) R is in third normal form
4. How many primary keys can a table have?
A) One
B) At least one, but not more than two
C) Between one and five
D) No limit

5. What is the definition of DBMS Architecture :


A) Three levels architecture: External , Conceptual, physical
B) Three levels for mapping different users‟ views ( User, logical , conceptual)
C) Three levels for DBMS internal mappings .( external, User, Internal)
D) None

2
Ministry of Communications and information Technology
Information Technology Institute

Q 2: Draw an ERD for the following case study:

A university registration office maintains data about the following entities:


(a) A program is described by number, title, credits, and syllabus.
(b) Course offerings, including course number, year, semester, section number,
instructor(s), timings, and classroom. Each course may have one or more prerequisites
courses. A program must consist of at least one course. Each course may be listed in one
program at most.
(c) Students, including student-id, name, and qualification.
(d) Instructors, including identification number, name, department and title.
(e)Scientific Departments which defined with Department id, name, internal phone,
number of instructors inside, and specialization.
Each department has many instructors, one of whom must be the head of department.
An instructor belongs to only one department. Each department offers many different
courses, and many instructors can teach one or more course. A student may enroll for
many courses offered by different departments. The system stores information about
student attendance and evaluation for each course.

3
Ministry of Communications and information Technology
Information Technology Institute

Q 3: Draw a Logical Schema (Mapping) for the following ERD:

4
Ministry of Communications and information Technology
Information Technology Institute

(Question 4) SQL:
Part 1
Q1: Evaluate the SQL statement

Select * from orders


Where cust_id = (select cust_id from customers
Where cust_name=‟Smith‟);
What’s the result when the query is executed?
A)

B)

C)

D) None of the above.


5
Ministry of Communications and information Technology
Information Technology Institute

Q2: Examine the description of the MARKS table:

STD_ID NUMBER
STUDENT_NAME TEXT
SUBJ1 NUMBER
SUBJ2 NUMBER

SUBJ1 and SUBJ2 indicate the marks obtained by a student in two subjects.
Examine this SELECT statement based on the MARKS table:

SELECT subj1+subj2 total_marks,id


FROM marks
WHERE subj1>AVG (subj1) AND subj2>AVG (subj2)
ORDER BY total_marks;

What is the result of the SELECT statement?

(A) The statement executes successfully and the student id and sum of all marks
for each student who obtained more than the average mark in each subject.
(B) The statement returns an error at the SELECT clause.
(C) The statement returns an error at the WHERE clause.
(D) The statement returns an error at the ORDER BY clause.

Q3: If you are writing a select statement to join three tables using join conditions,
what is the number of join conditions needed?

A) 0
B) 1
C) 2
D) 3

Q 4: Examine the description of the EMPLOYEE table:


EMP_ID NUMBER NOTNULL
LAST_NAME TEXT NOTNULL
FIRST_ NAME TEXT
DEPT_ID NUMBER
Which statement produces the number of different departments that have employee with
last name Ahmed?
A) Select count(*)
From employee
Where last_name=‟Ahmed‟;

B) Select count(dept_id)
From employee
Where last_name=‟Ahmed‟;
C) Select distinct (count(dept_id))
From employee
6
Ministry of Communications and information Technology
Information Technology Institute

Where last_name=‟Ahmed‟;

D) Select count(distinct dept_id)


From employee
Where last_name=‟Ahmed‟;

Q5: You need to display the last names of those employees who have the letter “A” as
the second character in their names.
Which SQL statement displays the required results?

A) SELECT last_name
FROM EMP
WHERE last_name LIKE „_A%‟;

B) SELECT last_name
FROM EMP
WHERE last name =‟*A%‟;

C) SELECT last_name
FROM EMP
WHERE last name =‟_A%‟;

D) SELECT last_name
FROM EMP
WHERE last name LIKE „*A%‟;

7
Ministry of Communications and information Technology
Information Technology Institute

(Question5) Normalization:
The following table represents the database of a system that stores data about all Car
Companies in Egypt. Model ID is the identifier for each model with a specific color.
Selling Price is the price of that model in the specified Company. Available Quantity is
the quantity of the model in this Company. Dealer is the distributer (‫ )موزع‬of the
specified model regardless of the Company. Company ID is the current Primary Key of
the table.

You are required to show the first, second and third normal forms.

Best luck.

Common questions

Powered by AI

Transitioning from second to third normal form eliminates transitive dependencies, ensuring that all the non-key attributes are dependent only on the primary key. For a database storing car companies and their models, this step is crucial to avoid redundancy and update anomalies, thereby ensuring that each piece of data is only influenced by the pertinent identifier .

In a nested query, the inner query executes first and only once before the outer query, which can significantly influence the performance and result of the query execution. This execution order can limit performance if the inner query returns a large result set or if it is computationally expensive .

DBMS architecture is defined as a three-level architecture consisting of the external, conceptual, and internal levels .

To determine the number of distinct departments where employees have the last name 'Ahmed', you would use the query: SELECT COUNT(DISTINCT dept_id) FROM employee WHERE last_name='Ahmed';. This query counts unique department IDs, thus providing the number of distinct departments .

Views in database management systems act as virtual tables that represent the results of a stored query. They do not have physical storage like tables; instead, their data are derived from base tables every time the view is accessed .

Joining three tables with fewer than two join conditions could lead to a Cartesian product, which means each row in one table will pair with every row in the other tables, possibly producing unintended results. To correct this, ensure there are sufficient join conditions—typically, n-1 join conditions are needed to properly associate these tables without redundancy .

Enforcing integrity constraints in a DBMS maintains consistency among rows in relations and preserves data integrity across tables. However, modifying these constraints can be complex because alterations might require reevaluating and adjusting the operational logic of the database to ensure ongoing integrity .

The presence of non-key functional dependencies in the relational schema R(A, B, C, D, E) indicates potential partial dependencies, suggesting it might not be in third normal form. Despite any primary key, having attributes dependent on a non-primary key attribute (as seen with C, D -> E and B -> C) means that the schema cannot be in second or third normal form .

Deleting all tuples in a table removes the data but retains the table structure, indexes, and any associated constraints, thereby maintaining the schema and its relationships. Dropping a table will remove the table and all its dependencies completely, affecting all relationships and requiring redefinition to restore its structure .

In SQL, using wildcards like '_' allows precise filtering of strings based on character positions. To find names with 'A' as the second letter, the pattern '_A%' is used, where '_' matches any character at the first position, and '%' matches any sequence of characters following the 'A' .

You might also like