Databases | SQL | Oracle | MS Access Answers

III. Query questions. (COVERING 11 ITEM, 67 marks IN TOTAL)

Help me

Please Read All Questions And Answer All 11 Question for me.

Table: Movies

Id   Title           Director         Year            Length_minutes
1   Toy Story        John Lasseter    1995                  81
2   A Bug's Life     John             1998                  95
3   Toy Story 2      John Lasseter    1999                  93
4   Monsters, Inc.   Pete Docter      2001                  92
5   Finding Nemo     Andrew Stanto    2003                  107
7   Cars             John Lasseter    2006                  117
9   WALL-E           Andrew Stanton   2008                  104
10  Up               Pete Docter      2009                  101
11  Toy Story 3      Lee Unkrich      2010                  103
12  Cars 2           John Lasseter    2011                  120
13  Brave            Brenda Chapman   2012                  102
87  WALL-G           Brenda Chapman   2042                  97

Table: Boxoffice

Movie_id    Rating    Domestic_sales        International_sales
5             8.2        380843261                555900000
12            6.4        191452396                368400000
3             7.9        245852179                239163000
9             8.5        223808164                297503696
11            8.4        415004880                648167031
1             8.3        191796233                170162503
7             7.2        244082982                217900167
10            8.3        293004164                438338580
4             8.1        289916256                272900000
2             7.2        162798565                200600000
13            7.2        237283207                301700000

1.      Create table Movies. It includes the domain of values associated with each attribute and integrity constraints. (7 marks)

2.      Write an SQL query that finds the title of each film. (5 marks)

3.      Write an SQL query that finds the movies released in the years between 2000 and 2010. (5 marks)

4.      Find all the WALL-* movies. (5 marks)

5.      List the last four Pixar movies released (ordered from most recent to least). (5 marks)

6.      Find the domestic and international sales for each movie. (6 marks)

7.      List all movies that were released in even number of years. (5 marks)

8.      Add the studio's new production, Toy Story 4 to the list of movies (you can use any director). (5 marks)

9.      The director for A Bug's Life is incorrect, it was actually directed by John Lasseter. (5 marks)

10.  This database is getting too big, let's remove all movies that were released before 2005. (5 marks)

11.  SELECT title, year FROM movies WHERE year < 2000 OR year > 2010; (6 marks)

Write the result:

Faisalabad Electric Supply Company (FESCO) distributes and supplies electricity to about 4.50 million customers within its territory with a population over 26 million under a distribution license granted by National Electric Power Regulatory Authority (NEPRA). The main determinant of electricity bill in Pakistan is the number of units consumed. Usually, electricity companies charge different prices as per kilowatt hour (kWh) to residential users, small commercial users and industrial companies etc according to the units consumed. For example, the approximate schedule of electricity tariffs for residential users is 5.79 Rs/kWh for 001-100 units, 8.11 Rs/kWh for 101-200 units, 10.20 Rs/kWh for 201-300 units, 17.60 Rs/kWh for 301-700 units, and 20.70 Rs/kWh for above 700 units. For commercial users, the approximate schedule of electricity tariffs is 18 Rs/kWh for sanctioned load less than 5 kW and 19.68 Rs/kWh for sanctioned load 5 kW and above. Similarly, for industrial users, the schedule of electricity charges is different.   Being a student of managerial economics, analyze the above case study carefully and identify which type of price discrimination is discussed in above case study. Also discuss the reason logically.

Consider the global relations EMP(ENAME, TITLE, CITY) and ASG(ENAME, PNAME, CITY, DUR). City in ASG is the location of the project of name PNAME (i.e., PNAME functionally determines CITY). Consider the local relations EMP1(ENAME, TITLE, CITY), EMP2(ENAME, TITLE, CITY), PROJ1(PNAME, CITY), PROJ2(PNAME, CITY) and ASG1(ENAME, PNAME,DUR). Consider query Q which selects the names of the employees assigned to a project in Rio de Janeiro for more than 6 months and the duration of their assignment.

(a) Assuming the GAV approach, perform query rewriting.

(b) Assuming the LAV approach, perform query rewriting using the bucket algorithm.

Consider the global relations EMP(ENAME, TITLE, CITY) and ASG(ENAME, PNAME, CITY, DUR). City in ASG is the location of the project of name PNAME (i.e., PNAME functionally determines CITY). Consider the local relations EMP1(ENAME, TITLE, CITY), EMP2(ENAME, TITLE, CITY), PROJ1(PNAME, CITY), PROJ2(PNAME, CITY) and ASG1(ENAME, PNAME,DUR). Consider query Q which selects the names of the employees assigned to a project in Rio de Janeiro for more than 6 months and the duration of their assignment.

(a) Assuming the GAV approach, perform query rewriting.

(b) Assuming the LAV approach, perform query rewriting using the bucket algorithm.

A development team is developing a special purpose application for some organization. The data is very large but relatively static. Data loss is an acceptable risk. Multiple users are accessing the application at the same time. File system and database system are two approaches which can be used to store data. Being a database expert, you are approached by the Team manager to help them in deciding which of the above mentioned data storage system will be more suitable for current scenario.

Suggest the storage system considering the above mentioned scenario with solid reasoning.

a)     Suppose you own a clotting brand company with your name, then how will you manage the data of your customers, orders, contracts, accounts, fixed and running expenses efficiently?

b)    Would you prefer electronic database over manual database? Why or Why not? List down at least three databases available in the market for commercial use.

c)     Design the database for your clotting brand company using MS Access. Design tables (as mentioned in part a) with necessary fields (must include primary and foreign keys) and draw the ERD diagram. (Paste the image of ERD diagram using snipping tool in your answer sheet). Use query to fetch any specific information of your interest from more than one table (Paste the complete image using snipping tool in your answer sheet).

development team is developing a special purpose application for some organization. The data is very large but relatively static. Data loss is an acceptable risk. Multiple users are accessing the application at the same time. File system and database system are two approaches which can be used to store data. Being a database expert, you are approached by the Team manager to help them in deciding which of the above mentioned data storage system will be more suitable for current scenario.

Suggest the storage system considering the above mentioned scenario with solid reasoning.

A development team is developing a special purpose application for some organization. The data is very large but relatively static. Data loss is an acceptable risk. Multiple users are accessing the application at the same time. File system and database system are two approaches which can be used to store data. Being a database expert, you are approached by the Team manager to help them in deciding which of the above mentioned data storage system will be more suitable for current scenario.

a)    Design the database for your clotting brand company using MS Access. Design tables (as mentioned in part a) with necessary fields (must include primary and foreign keys) and draw the ERD diagram. (Paste the image of ERD diagram using snipping tool in your answer sheet). Use query to fetch any specific information of your interest from more than one table (Paste the complete image using snipping tool in your answer sheet).

Rubrics (Scoring Guide)

- Knowledge = 40%

- Application = 30%

- Synthesis = 30% 

Question Statement

A development team is developing a special purpose application for some organization. The data is very large but relatively static. Data loss is an acceptable risk. Multiple users are accessing the application at the same time. File system and database system are two approaches which can be used to store data. Being a database expert, you are approached by the Team manager to help them in deciding which of the above mentioned data storage system will be more suitable for current scenario.

Suggest the storage system considering the above mentioned scenario with solid reasoning.

Note: Your answer should not be exceeded than 8 lines.