Ask Question, Ask an Expert

+1-415-315-9853

info@mywordsolution.com

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

This assignment consists of two 2 sections a design

This assignment consists of two (2) sections: a design document and a revised Gantt chart or project plan. You must submit both sections as separate files for the completion of this assignment. Label each file name accor ...

Assignmentthis must be your own unique work you cannot have

Assignment THIS MUST BE YOUR OWN UNIQUE WORK: you cannot have the exact same table as another student and you cannot turn in something you find online!Your work will be checked against a plagiarism database. The format m ...

Computer sciencein reference to your database design

Computer Science In reference to your Database Design Proposal: Proposal Outline, provide a narrative analysis of the customer and user needs by defining the customers and users as well as describing their individual nee ...

Data warehouse1 review the submission hokie resort

Data Warehouse 1. Review the submission Hokie Resort situation description to understand the problem domain(see Business Situation Description below) 2. Write the three most important questions that management must answe ...

Assignment advanced relational databaseproject- advance

Assignment: Advanced Relational Database PROJECT- Advance SQL Overview: Using the knowledge garnered thus far, please solve all of the below problems. Please run the attached SQL script file at the SQL*PLUS prompt. This ...

Database systems assignment - student enrolment database a

Database Systems Assignment - Student Enrolment Database A. Task Description 1. Overview Your task is to create and test a database in MySQL using PhpMyAdmin. You are provided with a scenario and supporting documents, de ...

Replacement china incproject descriptionyou work as an

Replacement China, Inc. Project Description: You work as an associate database manager at Replacement China, Inc. This firm specializes in finding difficult-to-replace, no-longer manufactured china, crystal, silver, and ...

Select one of the four primary categories of terrorist

Select one of the four primary categories of terrorist hazards (Chemical, Biological, Radiological/Nuclear, Explosives). Provide a history of its use as a weapon of mass destruction (WMD); describe how it is made and dis ...

Database design discusion 150 wordsnormalizationplease

Database Design Discusion (150 words) Normalization Please respond to BOTH of the following questions: Question A In your own words discuss the benefits of normalization. Question B Do you think we should normalize our d ...

Unlimited pickers is a group of workers who have joined

Unlimited Pickers is a group of workers who have joined together to provide harvesting services to farmers who need to have their crops brought in. The organization has many teams of workers who travel from location to l ...

  • 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

Section onea in an atwood machine suppose two objects of

SECTION ONE (a) In an Atwood Machine, suppose two objects of unequal mass are hung vertically over a frictionless

Part 1you work in hr for a company that operates a factory

Part 1: You work in HR for a company that operates a factory manufacturing fiberglass. There are several hundred empl

Details on advanced accounting paperthis paper is intended

DETAILS ON ADVANCED ACCOUNTING PAPER This paper is intended for students to apply the theoretical knowledge around ac

Create a provider database and related reports and queries

Create a provider database and related reports and queries to capture contact information for potential PC component pro

Describe what you learned about the impact of economic

Describe what you learned about the impact of economic, social, and demographic trends affecting the US labor environmen