Based on the schema write sql statements

Assignment Help Database Management System
Reference no: EM1380689

Part 1: True or False, please explain 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.

 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, write 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)

Part 3: Questions

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.

Reference no: EM1380689

Questions Cloud

Creating an exception class and applet file : Create an applet document that prompts the user for an ID number and an age. Construct an Exception class and throw an Exception of that class if the ID is not in the range of valid ID numbers.
Find article related to capital structure summarize it : Find article related to Capital Structure 2012 summarize it also explain why it is important also explain problems also write name of author also name of website.
Explain marketing strategy : Imagine that your organization is seeking a marketing strategy to promote the development and implementation of a well-baby program
Determine the transmission rate : Assume two TCP connections are available over some bottleneck link of rate R bps. Both connections have a huge document to send in the similar direction over the bottleneck link
Based on the schema write sql statements : Based on the schema write SQL statements and provide the equivalent relational algebra expressions - relational algebra expressions return and give the equivalent SQL statements.
Performance organization and hrm technology : Performance Organization and HRM Technology- Define high-performance work systems, and identify the elements of such a system. Summarize the outcomes of a high-performance work systems.
Close working relationships need to exist between companies : Obviously, close working relationships need to exist between companies that design, manufacture, and market surgical implants and the surgeons who use those implants
Question about designing a database : As we start designing a database for implementation should we use the latest and greatest technology? Does the user need a flat-file or object-oriented database?
Segment analysis section : Segment Analysis Section- For this Discussion Forum, please document all of the steps needed to create a specific strategic group map for the beer industry. Then create a strategic group map

Reviews

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd