Assessment >>> Given the following ER diagram generate the relational model using notation: RelationName >>> Relational database systems
1.
Question 1
Given the following ER diagram, generate the relational model using notation: RelationName(fieldpk, field2, field3fk)
FAMILIAR (IDFAMILIAR,NAMEPATIENT)
DISEASE (IDDISEASE,TYPEDISEASE,NAMEDISEASE)
ISFAMILIARWITH (IDPATIENT,IDFAMILIAR,RELATIONSHIP)
SUFFERSFROM (IDPATIENT,IDDISEASE)
DISEASE (IDDISEASE,TYPEDISEASE,NAMEDISEASE)
ISFAMILIARWITH (IDPATIENT,IDFAMILIAR,RELATIONSHIP)
SUFFERSFROM (IDPATIENT,IDDISEASE)
=============================================
4.
Question 4
Why would you choose a database system instead of simply storing data in operating system files?
– only a small amount of data
– no need for concurrency control / access control / recovery
– efficient routines for searching, inserting and deleting data
– concurrency control
– recovery from failures
– access control
=============================================
6.
Question 6
What is logical data independence?
3.
Question 3
Using the relational model generated for the Patients system, answer the following query with SQL.
Given the name of a disease, such as cancer, know the name and address of all patients who have suffered, use joins, not subquery.
5.
Question 5
When would it make sense not to use a database system?
– concurrency control
– recovery from failures
– access control
– only a small amount of data
– data is not structured (for example only pictures/music without metadata)
– no need for concurrency control / access control / recovery
=============================================
11.
Question 11
Consider following tables
Give following query “Employee name, department name of those employees that do not have any customers” in SQL-query.
8.
Question 8
What are some responsibilities of a DBA?
– development of applications
– network security
– checking final user application requirements
– coordinating and monitoring the use of the database
– security
– backups
– usability and efficiency of the database
– acquiring new software and hardware
– understanding query optimization
=============================================
2.
Question 2
Using the relational model generated during previous exercise, answer the following query with SQL.
Given the name of a disease, know the name of all patients for whom, their father or mother have suffered the disease. Use subquery, not join.
9.
Question 9
Consider following SQL-query, where SSN attribute is employee number and SUPERSSN attribute is employees managers employee number.
What does the query do?
7.
Question 7
What is physical data independence?
10.
Question 10
Consider following tables
Give following query “Employee name, department name and salary of those employees whose have the highest salary in the department” in SQL-query.
