INTRODUCTION TO DATABASE (IT244) ASSIGNMENT 1 (LAST DATE TO SUBMIT: 20/02/2016)

Question

INTRODUCTION TO DATABASE (IT244)

ASSIGNMENT 1

(LAST DATE TO SUBMIT: 20/02/2016)

Q1: Consider the below schema for a library database and write relational algebra expressions for the following queries

1.Author ( authorname, citizenship, birthyear)

2.Book (isbn, title, authorname)

3.Topic (isbn, subject)

4.Branch (libname, city)

5.Instock (isbn, libname, quantity)

1.Give all authors’ names born after 1940.                                                                    [0.25 Point]

2.Give the names of libraries in Sydney.                                                                       [0.25 Point]

3.Give the cities where each book is held.                                                                     [0.25 Point]

4.Give the title of each book on the topic of either alcohol or drugs.                           [0.25 Point]

5.Give the title and author of each book of which at least two copies are held in a branch located in Melbourne.   

Q2. What are the responsibilities of a DBA? If we assume that the DBA is never interested in running his or her own queries, does the DBA still need to understand query optimization? Why?      [1.5 Marks]

Q3: What are the drawbacks of using file system over database management system?        [1 Marks]

Q4: Consider the advisor relation shown in Figure 2, with s_id as the primary key of advisor. Suppose a student can have more than one advisor. Then, would s_id still be a primary key of the advisor relation? If not, what should the primary key of advisor be?                                                                 [1 Marks]

Schema Diagram for University Database.

Explanation / Answer

Q1 Answer

1. all authors born after 1940.

SELECT authorname FROM Author WHERE birthyear>1940

or

    SELECT a.authorname FROM Author a WHERE a.birthyear>1940

2. Give the names of libraries in Sydney.

SELECT libname FROM Branch WHERE city= 'Sydney'

3. Give the cities where each book is

SELECT Book.isbn, city FROM Book, Instock, Branch WHERE Book.isbn = Instock.isbn and Instock.libname = Branch.libname

or

SELECT isbn, city FROM Book JOIN Instock USING (isbn) JOIN Branch USING (libname)

4. Give the title of each book on the topic of either alcohol or drugs.

SELECT title FROM Book JOIN Topic using (isbn) WHERE subject = 'alcohol' or subject = 'drugs'

or

SELECT title FROM Book WHERE isbn IN (SELECT isbn FROM Topic WHERE subject = 'alcohol' or subject = 'drugs')

5. Give the title and author of each book of which at least two copies are held in a branch located in Melbourne.

SELECT title, authorname FROM Book JOIN Instock using (isbn) JOIN Branch using (libname)

WHERE city = 'Melbourne' and quantity>=2

or

SELECT title, authorname FROM Book WHERE isbn IN (SELECT isbn FROM Instock WHERE quantity >=2 AND libname IN (SELECT libname FROM Branch WHERE city = 'Melbourne') )

Q2. answer

* Designing the logical and physical schemas, as well as widely-used portions of the external schema.

* Security and authorization.

* Data availability and recovery from failures.

* Database tuning: The DBA is responsible for evolving the database, in particular the conceptual and physical schemas, to ensure adequate performance as user requirements change.

A DBA needs to understand query optimization even if s/he is not interested in running his or her own queries because some of these responsibilities (database design and tuning) are related to query optimization. Unless the DBA understands the performance needs of widely used queries, and how the DBMS will optimize and execute these queries, good design and tuning decisions cannot be made.

Q3) Answer

The database approach offers a number of potential advantages compared to traditional file processing systems.

1. Program-Data Independence: The separation of data descriptions from the application programs that use the data is called Data independence. With the database approach. Data descriptions are stored in a central location called the repository. This property of database systems allows an organization’s data to change without changing the application programs

that process the data.

2. Data Redundancy and Inconsistency: In File-processing System, files having different formats and application programs may created by different programmers. Similarly different programs may be written in several programming languages. The same information placed at different files which cause redundancy and inconsistency consequently higher storage and access cost. For Example,the address and telephone number of a person may exist in two files containing saving account records and checking account records. Now a change in person’s address may reflect the saving account records but not any where in the whole system. This results the data inconsistency. One solution to avoid this data redundancy is keeping the multiple copies of same information, replace it by a system where the address and telephone number stored at just one place physically while it is accessible to all applications from this itself. DBMS can handle Data Redundancy and Inconsistency.

3. Difficulty in accessing Data: - In Classical file organization the data is stored in the files.Whenever data has to be retrieved as per the requirements then a new application program has to be written. This is tedious process.

4. Data isolation: - Since data is scattered in various files, and files may be in different formats, it is difficulty to write new application programs to retrieve the appropriate data.

5. Concurrent access: - There is no central control of data in classical file organization. So, the concurrent access of data by many users is difficult to implement.

6. Security Problems: - Since there is no centralized control of data in classical file

organization. So, security, enforcement is difficult in File-processing system.

7. Integrity Problem: - The data values stored in the database must satisfy certain types of consistency constraints. For example, the balance of a bank account may never fall below a prescribed amount. These constraints are enforced in the system by adding appropriate code in the various application programs. However, when new constraints are added, it is difficult to change the programs to enforce them. The problem is compounded when constraints involve several data items from different files.

8. Improved Data Sharing: - A database is designed as a shared corporate resource.

Authorized internal and external users are granted permission to use the database, and each user is provided one or more user views to facilitate this use. A user view is a logical description of some portion of the database that is required by a user to perform some task.

9. Increased Productive of Application Development: - A major advantage of the database approach is that it greatly reduces the cost and time of developing new business applications.

There are two important reasons that data base applications can often be developed much more rapidly than conventional file applications.

a) Assuming that the database and the related data capture and maintenance applications have already been designed and implemented, the programmer can concentrate on the specific functions required for the new application, without having to worry about file design or low-level implementation details.

b) The data base management system provided a number of high-level productivity tools such as forms and reports generators and high-level languages that automate some of the activities of database design and implementation.

Disadvantages:-

1. It occupies more amount of space. It is generic

2. More time they access data.

3. More complex and expensive hardware and software resources are needed.

4. Sophisticated security measures must be implemented to prevent unauthorized access of

sensitive data in online storage.

What are the disadvantages of File Oriented System? A.

The typical file-oriented system is supported by a conventional operating system. Permanent records are stored in various files and a number of different application programs are written to extract records from and add records to the appropriate files. The following are the disadvantages of File-Oriented System:

(i). Data redundancy and Inconsistency: Since files and application programs are created by different programmers over a long period of time, the files are likely to be have different formats and the programs may be written in several programming languages. Moreover, the same piece of information may be duplicated in several places. This redundancy leads to higher storage and access cost. In addition, it may lead to data inconsistency, i.e. the various copies of same data may no longer agree.

(ii). Difficulty in accessing data: the conventional file processing environments do not allow needed data to be retrieved in a convenient and efficient manner. Better data retrieval system must be developed for general use.

(iii). Data isolation: Since data is scattered in various files, and files may be in different formats, it is difficult to write new application programs to retrieve the appropriate data.

(iv). Concurrent access anomalies: In order to improve the overall performance of the system and obtain a faster response time, many systems allow multiple users to update the data simultaneously. In such an environment, interaction of concurrent updates may result in inconsistent data.

(v). security problems: Not every user of the database system should be able to access all the data. For example, in banking system, payroll personnel need only that part of the database that has information about various bank employees. They do not need access to information about customer accounts. It is difficult to enforce such security constraints.

(vi). Integrity problems: The data values stored in the database must satisfy certain types of consistency constraints. For example, the balance of a bank account may never fall below a prescribed amount. These constraints are enforced in the system by adding appropriate code in the various application programs. When new constraints are added, it is difficult to change the programs to enforce them. The problem is compounded when constraints involve several data items for different files.

(vii). Atomicity problem: A computer system like any other mechanical or electrical device is subject to failure. In many applications, it is crucial to ensure that once a failure has occurred and has been detected, the data are restored to the consistent state existed prior to the failure.

Q4 answer

No, s_id would not be a primary key, since there may be two (or more) tuples for a

single student, corresponding to two (or more) advisors. The primary key should then be s_id,

i_id

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.