Ask Question, Ask an Expert


Ask DBMS Expert

Home >> DBMS

 Problem Statement Part 1: True or False, please describe why.

1, SQL (Structured Query Language) is both DDL (Data Definition Language) and DML (Data Manipulation Language).

2, View is not updatable.

3, For Character data type, value NULL is equivalent to empty string ''.

4, A table has only one primary key, but it can have more than one foreign keys.

5, In SQL, an asterisk (*) can be used to express "all columns".

6, If a schema satisfies BCNF, then it also satisfies 3NF.

7, A secondary index can be either dense or sparse.

8, Given a hash function h and two search-key values, Ki and kj, if ki≠kj, then h(ki)≠h(kj).

9, ΠLname, Fname, Salary(σsalary>30000(Employee)) is equivalent to σsalary>30000(ΠLname, Fname, Salary(Employee)).

10, since Natural-join operations are associative, (E1 E2) E3 = E1 (E2 E3), the ordering of natural-join is not important for query optimization.

 Problem Statement Part 2: Case study

The following tables form part of a database held in a relational DBMS:

 Employee(Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)

Department(Dname, Dnumber, Mgr_ssn, Mgr_start_date)

Project(Pname, Pnumber, Plocation, Dnum)

Works_on(Essn, Pno, Hours)

The underlined attribute(s) in each relational schema is primary key.

Employee contains employee details, Super_ssn is the SSN of supervisor, which is a foreign key that refers to Ssn in table Employee, and Dno is the department number, which is another foreign key that refers to Dnumber in table Department.

Department contains department details and Mgr_ssn is SSN of the department manager, which is a foreign key that refers to Ssn in table Employee.

Project contains project details, and Dnum is a foreign key which refers to Dnumber in table Department.

Works_on records who work on which project, Essn is a foreign key that refers to Ssn in table

Employee, and Pno is another foreign key that refers to Pnumber in table Project.

 Based on the schema defined above, prepare SQL statements to answer the following queries:

1, Retrieve the birth date and address of the employee(s) whose name is 'John B. Smith'.

2, Retrieve the name and address of all employees who work for the 'Research' department.

3, For each employee, retrieve the employee's first name and last name and the first name and last name of his or her immediate supervisor.

4, Find the sum of the salaries of all employees, the maximum salary, the minimum salary, and the average salary.

5, For each project, retrieve the project number, the project name, and the number of employees who work on that project.

Describe what the following SQL statements return and give the equivalent relational algebra expressions.

6, Select Fname, Lname, Sex, Address

From Employee;

7, Select Fname, Lname

From Employee

Where Sex='M';

Describe what the following relational algebra expressions return and give the equivalent SQL statements

8, ΠLname, Fname, Salarysalary>30000(Employee))

9, ΠDname, Lname, Fname(Department Mgr_ssn = SsnEmployee)

 Problem Statement Part 3:  Problems

1, Use Armstrong's axioms to prove the soundness of the union rule. Union rule: If a → b holds and a → c holds, then a → b c holds.

2, For B+ Tree below, show the steps involved in the following queries:

 a. Find records with a search-key value which is great than 18.

 b. Find records with a search-key value which is between 5 and 19, inclusively.

DBMS, Programming

  • Category:- DBMS
  • Reference No.:- M938177

Have any Question? 

Related Questions in DBMS

Realtor database data redundancy and anomaliesbullconduct

Realtor Database: Data Redundancy and Anomalies • Conduct an in-depth examination and analysis of the database, Realtor.accdb. Apply the concepts of database systems that you have learned this week. For example, you may ...

Assignmentoverviewthis assignment gets you started thinking

Assignment Overview This assignment gets you started thinking like a database designer. You will learn concepts from chapters 1 through 5 and and implement them using MS Access. You should complete the following activiti ...

Database design and implementationtask 1 - database

Database Design and Implementation Task 1 - Database Design Your first task is to design a database for the scenario detailed on the following pages. Your final database design should comprise of approximately 10 entitie ...

Database relationsusing the invoice table structure shown

Database Relations Using the INVOICE table structure shown in Table, do the following: ATTRIBUTE NAME SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE INV_NUM 211347 211347 211347 211348 211349 PROD_NUM A ...

Home libraryisbntitleauthor Home Library I SBN Title Author LastName Author_FlrstName

Home Library I SBN Title Author LastName Author_FlrstName Publisher Date Edition Media 369852 Cosmos Sagan Carl Random House 1980   Book 741258 No Secrets Simon Carly Elektra 1972   CD 654789 Symphony No 3 Dur Eroica Op ...

Data base 1 hypertensionobjective to determine whether or

Data Base 1: Hypertension Objective: To determine whether or not the community self-management program is effective to promote health and reduce blood pressure level in patients with hypertension Rationale In low-to-midd ...

Database design continueduse the attached database to

Database Design Continued Use the attached database to complete the following assignment: You will be modifying the frmMainMenu to call the caption of each button from the table MainMenu and form or the report. 1) Create ...

When 100 ml of m10 h2so4 is mixed with 500 ml of my10 naoh

When 100 ml of M/10 H2SO4 is mixed with 500 ml of My/10 NaOH then nature of resulting solution and normality of excess reactant left is

Physical database design assignment query optimisationthis

Physical Database Design Assignment: Query Optimisation This assignment aims to develop your skills optimizing query. It involves identifying the suitable methods for structuring the data and writing the query. First, yo ...

Project guidelinesoverviewthe final project for this course

Project Guidelines Overview The final project for this course is the creation of a database proposal and implementation plan report that will address the needs of a given scenario. The purpose of this assessment is to as ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

A cola-dispensing machine is set to dispense 9 ounces of

A cola-dispensing machine is set to dispense 9 ounces of cola per cup, with a standard deviation of 1.0 ounce. The manuf

What is marketingbullwhat is marketing think back to your

What is Marketing? • "What is marketing"? Think back to your impressions before you started this class versus how you

Question -your client david smith runs a small it

QUESTION - Your client, David Smith runs a small IT consulting business specialising in computer software and techno

Inspection of a random sample of 22 aircraft showed that 15

Inspection of a random sample of 22 aircraft showed that 15 needed repairs to fix a wiring problem that might compromise

Effective hrmquestionhow can an effective hrm system help

Effective HRM Question How can an effective HRM system help facilitate the achievement of an organization's strate