Exercise 1-2 A small library keeps a roster of who will be at the desk each day. They have a database table as shown in Figure 1-8. week_start Mon - Tue - Wed - Thur - Fri 17/10/2011 Jane Sue George Sue Jane 24/10/2011 Jane Sue Linda Sue Lee 31/10/2011 Sue Sue Lee | George George Figure 1-8. An initial database table to record roster duties What problems can you foresee in making good use of this information? Suggest some better ways that this information could be stored.

Exercise 1-2 A small library keeps a roster of who will be at the desk each day. They have a database table as shown in Figure 1-8. week_start Mon - Tue - Wed - Thur - Fri 17/10/2011 Jane Sue George Sue Jane 24/10/2011 Jane Sue Linda Sue Lee 31/10/2011 Sue Sue Lee | George George Figure 1-8. An initial database table to record roster duties What problems can you foresee in making good use of this information? Suggest some better ways that this information could be stored.

A Guide to SQL

9th Edition

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Philip J. Pratt

Chapter2: Database Design Fundamentals

Section: Chapter Questions

Problem 5TD

See similar textbooks

Similar questions

Think about a student database with the StudentID as the main key and the Major, Age, MaritalStatus, and HomeZipCode indexes (all secondary keys). If the institution requested a list of MIS or computer science majors who were over 25, married, and from the 45462 zip code OR computer engineering majors who were single and from the 45462 zip code, then let's pretend they were both over 25 and from the 45462 postcode. How can indexes be used to guarantee that only the data that satisfies this criteria is accessible to users?
A student database may include indexes on fields such as major, age, marital status, and home zip code in addition to the main key, which is the student's unique identifier (all secondary keys). Let's assume the school wants a list of MIS or CS majors over the age of 25 who are married and from the 45462 zip code OR a list of CS majors under the age of 25 who are single and from the 45462 the code. Is there a way to utilize indexes to restrict access to just the records that meet this criterion?
Airline Company An airline company is interested in implementing a database system to manipulate the flights thatit organizes.The airline company has a certain number of airplanes. Each airplane has a unique code, anumber of seats and a production year. An airplane belongs to a certain airplane model. Eachmodel has a unique model number and is provided by a certain company. We have differentairplanes of each model. Each flight is identified by a flight number, a scheduled departure timeand a scheduled arrival time. We want also to keep track of the airport from which the flight isscheduled as well as the destination airport. Each airport is identified by a unique code, its nameand the country in which it is located. Journeys of each flight are organized on a daily basis.Each journey is identified by a date, its actual departure time, its actual arrival time, and thenumber of passengers, in addition to the airplane that performs the journey. We want to keeptrack of the seats occupied,…
Today almost every commercial database is based on O the hierarchical model O the linked-list model O the object-oriented model lists the relational model Given the relations: STUDENT (SID. StudentName, Major, AdvisorID) ADVISOR (AdvisorID, AdvisorName, Office, Phone) such that each student is assigned to one advisor, which of the following is true? O Major is a candidate key. O Phone is a candidate key. AdvisorName is a determinant. O AdvisorID is a foreign key. O SID is both a primary key and a foreign key.
Question 1 A city college would like to maintain their academic information in a MySql database system. The following are the interested information: Courses: Catalog # (CIT 170 for instance) Credit Hours; Name of the Course (Database Design Fundamentals for instance) Course Descriptions Prerequisite Components (Lecture, or Laboratory for instance) Classes: Class # Class Name (usually the course name, M 170 for instance) Start Date End Date Meeting time (9:30 am ~ 12:15 pm, for instance) Days in a week (Monday) Term (Spring 2018, for instance) Location (TIE Building for instance) Room (UB 301, for instance) Instructor Online status (online, or in-person) Faculty: Employee Id (001880301, for instance) First Name Last Name Title (professor, for instance) Email Phone Office Location (TIE building, for instance) Room (UB303, for instance) Student: Student ID First Name Last Name Plan Sub Plan Advisor Current GPA Grade of each course The Term you got the grade for a course. The…
Assume a database of Employees of a company have been given as shown below:Employee: < ID, Name, ProjCode, Salary>Projects: <projID, ProjName, Budget> For each project, count the number of employees who are paid less than the average salary of that project. Determine if there is any project with a budget less than the total salary paid to its employees. If yes, find the name of the project and its budget.
Consider the following database scheme Employee (SSN, fname, lname, salary, deptno) Department (deptno, dname, total_salary) Where total salary is a derived attribute that represents the total salary of employees working in a specific department. Write a trigger to compute the total_salary in the event of inserting one or more new employee record(s) in the employee table.
Giving the following schema for a supermarket database, specify the domain constrains for each attribute. CUSTOMERS (CustomerID , First_Name, Last_Name) EMPLOYEES (EmployeeID, Name, Email, PhoneNo) INVOICES( InvoiceID, Amount_Paid, Invoice_Date)
Indexing Consider a relational database that consists of the relational tables created by the following CREATE TABLE statements: CREATE TABLE PART( P_PARTKEY NUMBER(12) NOT NULL, P_NAME VARCHAR(55) NOT NULL, P_RETAILPRICE NUMBER(12,2) NOT NULL, CONSTRAINT PART_PEKEY PRIMARY KEY (P_PARTKEY) ); CREATE TABLE SUPPLIER( S_SUPPKEY NUMBER(12) NOT NULL, S_NAME CHAR(25) NOT NULL S_NATIONKEY NUMBER(12) NOT NULL, S_PHONE CHAR(15) NOT NULL, CONSTRAINT SUPPLIER_PKEY PRIMARY KEY (S_SUPPKEY) ); CREATE TABLE PARTSUPP( PS_PARTKEY NUMBER(12) NOT NULL, PS_SUPPKEY NUMBER(12) NOT NULL, PS_SUPPLYCOST NUMBER(12,2) NOT NULL, CONSTRAINT PARTSUPP_PKEY PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY), CONSTRAINT PARTSUPP_FKEY1 FOREIGN KEY (PS_PARTKEY) REFERENCES PART(P_PARTKEY), CONSTRAINT PARTSUPP_FKEY2 FOREIGN KEY (PS_SUPPKEY) REFERENCES SUPPLIER(S_SUPPKEY) ); Determine what index should be created to improve the performance of the queries listed below in the best possible way. Consider each one of the…
DATABASE SYSTEMS 16.Consider a relation R = (A,B,C,D,E) with the following functional dependencies: {C,E → D, D → B, C → A} and identify a candidate key. None of them {C,E,D} {C,E,B} {C,E} 17.A join in which tuples from R1 that do not have matching values in the common attributes of R2 are also included in the result relation. R1 left outer join R2 R1 semi join R R1 equi join R2 R1 natural join R2 18.When two transactions that access the same database items have their operations interleaved in a way that makes the values of some database items incorrect this problem is call? none of them dirty read summary problem the lost update problem 19.Protect the database from inconsistencies and data loss Database recovery Database Transaction & Concurrency control Concurrency control Concurrency control & Database recovery 20.Let E1 and E2 be two entity types in an entity relationship diagram. E1 have all three simple single-valued attributes while E2 have two simple…
Suppose that you are a manufacturer of product ABC, which is composed of parts A, B, and C. Each time a new product ABC is created, it must be added to the product inventory, using the PROD_QOH in a table named PRODUCT. Also, each time the product is created, the parts inventory, using PART_QOH in a table named PART, must be reduced by one each of parts A, B, and C. The sample database contents are shown in Table P10.1. Given the preceding information, answer Questions a through e. How many database requests can you identify for an inventory update for both PRODUCT and PART? Using SQL, write each database request you identified in Step a. Write the complete transaction(s). Write the transaction log, using Table 10.1 as your template. Using the transaction log you created in Step d, trace its use in database recovery.
Suppose that you are a manufacturer of product ABC, which is composed of parts A, B, and C. Each time a new product ABC is created, it must be added to the product inventory, using the PROD_QOH in a table named PRODUCT. Also, each time the product is created, the parts inventory, using PART_QOH in a table named PART, must be reduced by one each of parts A, B, and C. The sample database contents are shown in Table P10.1.Given the preceding information, answer Questions a through e.a. How many database requests can you identify for an inventory update for both PRODUCT and PART?b. Using SQL, write each database request you identified in Step a.c. Write the complete transaction(s).d. Write the transaction log, using Table 10.1 as your template.e. Using the transaction log you created in Step d, trace its use in database recovery.

SEE MORE QUESTIONS

Recommended textbooks for you

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

SEE MORE TEXTBOOKS