Final, questions PDF

Preview

Summary

final exam...

Description

MALTEPE UNIVERSITY DEPARTMENT OF SOFTWARE ENGINEERING

SE 307 – Database Management Systems Final Exam Name Lastname

:

Student No

:

Academic Year

: 2019-2020

Semester

: Summer

Date

: 25 August 2020

Exam Place

:

Duration

: 75 minutes

Instructor

: Assist. Prof. Dr. Volkan TUNALI

Question

1

2

3

4

Total

Points

50

20

15

15

100

Grade

RULES Write your name and lastname to the exam booklet prior to starting the exam. Answer all questions. Write your answers in the spaces provided. If needed to, you can use the last page to complete some of your answers. If your answers cannot be read, they cannot be graded.  Only this exam booklet and a calculator can be on your desk. Any other device such as cell phone, PDA, tablet, etc. is not permitted in any place that you can see them and will be treated as an attempt to cheat.  Exchanging any material including erasers, pencils is not permitted during the exam.  If any of the above rules is violated, it will be treated as an attempt to cheat or cheating during the exam.

  

No

Programme Key Learning Outcomes

Q1

Q2

Q3

Q4

1

1

2 3

To be able to define problems in life by indentifying functional and nonfunctional requirements the software is to satisfy.

X

X

To be able to define the other constituents of software architecture, X

components, interface and system. To be able to develop software by coding, verifying, doing unit testing and debugging.

X

X

X

X

To be able to solve software maintenance problems stemming from 4

working environment changes, newuser demands and the emergence of software errors that occur during the execution. To have knowledge in the area of software requirements analysis,

5

process planning, output specification, resource planning, risk management and quality planning. To be able to identify, evaluate, measure and manage changes in

6

software development by applying software engineering processes and release software versions systematically.

7

To be able to use various tools and methods to carry out the software design, testing, maintenance and development activities.

X

X

To have knowledge of basic quality metrics, software life cycle, 8

software quality, quality model, and be able to use them to develop, verify and test software. To have knowledge in other disciplines that have common boundaries

9

with software engineering such as computer engineering, management, mathematics, ergonomics and systems engineering. To be able to grasp software engineering culture and ethics and have

10

11 12

the basic information of applying them to the software engineering for the good of society. To be able to use a foreign language to follow related field publications and communicate with colleagues. To be able to demonstrate software engineering profession at international level.

The following is the relational database diagram of Northwind-2003 Mini database. Answer the SQL questions using this database.

2

1. Write an SQL query to: a. List all products whose unit prices are higher than the average price of all products, selecting only product ID, product name, and unit price. (10 points) 3

b. Make 10% increase in the unit price of all products whose category name is “Beverages”. (10 points)

c. Delete all customer records who have not given any order. (10 points)

d. List which customer has ordered which category of products in what quantity in total, selecting only Customer Name, Product Category Name, and Total Order Quantity. (10 points)

e. List Total Order Quantity by Order Year and Month. (10 points)

2. Write SQL queries to delete the customer whose CustomerID is known to be “ANTON”. Due to the foreign key relationships, a single delete query from the Customers table will not work. So, you will need to delete related rows from related tables in a correct order with at most 3 delete queries. If you use more than 3 delete queries, or if you delete rows in an incorrect order, you will 4

not get partial points! Don’t forget that the only data you can use is the CustomerID “ANTON” and nothing else. (20 points)

3. Create a Stored Procedure to select all customer records for the given country name passed as the procedure parameter (15 points).

4. Given the dependency diagram shown below, identify and discuss each of the indicated dependencies. Then, perform normalization until you get tables in 3NF, showing the dependency diagrams for each table. (15 points)

5

6...