Database Design Assignment Question

Tutorial 7

Questions:

1. What are the main strategies for system acquisition?
   • Custom Development – make it
   • Packaged System – buy it
   • Outsourcing – hire someone to do it

What are the selection criteria for them?

• Business need
• In-house experience
• Project skills
• Project management
• Time frame

2. What are the main types for system architectures?

• Server based architechure
• Client based architecture
• Cloud as the server

What are the selection criteria for them?

• Operational Requirements-Technical environment requirements, System integration requirements, Portability requirements, Maintainability requirements
• Performance Requirements-Speed requirements, Capacity requirements, Availability and Reliability requirements
• Security Requirements-System value estimate, Access control requirements, Encryption and authentication requirements, Virus control requirements
• Cultural and Political Requirements-Multilingual requirements, Customization requirements,Making unstated norms explicit, Legal requirements

3. Give another example of attributes over relationships. Draw the ER diagram for it.

In the ER diagram, attributes are itemised inside the entity or relationship rectangles

4. Sometimes the business rules related to ERD are vague and unclear. How to address this issue?

5. Write an SQL statement to select the names and emails of all students and all staff involved in the unit 300977.

6. Write an SQL statement to find out the guest who has booked at least 3 rooms in the same hotel by using nested/sub queries.

You should put your answers to these questions.

(2 marks) Practices:

1. (1 mark) SAD – Entity Relationship Diagram (ERD). Draw an Entity Relationship Diagram for the final project ( i.e., the MICT course database) based on the business rules obtained in the analysis phase.

Note: You need to submit your ERD, which should be included in the final project report. Make sure that you

• have identified the basic entities, relationships and their associations; Note that it’s very crucial to link logically closer entities together;
• have identified the basic attributes needed in order to meet the business requirements identified in the analysis phase;
• have correctly specified the constraints;
• have identified the primary keys of entities;
• have some advanced features, e.g., weak entities, attributes over relationships and generalisation/specialisation.

1. (1 mark) SQL – outer join and nested query.
   1. Select the guest names and prices of the rooms they booked, if any.

SELECT *

FROM room RIGHT OUTER JOIN guest

ON room.guestNo=guest.guestNo;

1. Select the guest names and the overall prices of the rooms they booked, if any.

1. Create a new table staff with the attributes

(staffNo,staffName,email,address,salary) and specify that staffNo is the primary key and staffName must be not null.

CREATE TABLE staff (

staffNo INTEGER NOT NULL,

staffName VARCHAR(200) NOT NULL

staffEmail VARCHAR(200)

staffAddress VARCHAR(200)

staffSalary

PRIMARY KEY (staffNo)

1. Insert some values into staff table as follows:

1. Show all staff names and guest names.

SELECT guestName FROM guest

UNION

SELECT staffName FROM staff;

1. Select the name of someone who is both a staff and a guest using the intersect keywords. Is there some issue with this?

(SELECT guestName FROM guest)

INTERSECT

(SELECT staffName FROM staff);

1. Select those staffs who are not guests.

(SELECT staffName FROM staff)

EXCEPT

(SELECT guestName FROM guest);

1. Select all rooms with a price below half of the more expensive room.

1. Select all the guest names who have booked a room with a price above average.

SELECT * FROM room

WHERE price> (SELECT avg(price) FROM room);

1. Select the guest names who have booked more than 3 rooms.

SELECT guestName FROM guest,

(SELECT count(*) AS rooms, guestNo FROM room GROUP BY guestNo) AS count

WHERE guest.guestNo=count.guestNo AND rooms>3;

Tutorial 8

• mark) Questions:

1. In logical database design, why there is a need to introduce a new relation for many-to-many relationship in ERD?

Because One or both of these foreign keys will also form the primary key of the new relation, possibly in combination with other attributes

2. Describe how to convert a generalization/specialization in ERD into relations in GRD.

Strong entity- create relations that includes all simple attributes.

Weak entity- create relation that includes all simple attributes (primary key still has to be identified after the relationship with each owner entity has been mapped).

Multi-valued attribute- create a relation to represent the multi-valued attribute and post a copy of the primary key of the owner entity into the new relation to act as a foreign key.

3. What are user interface and system interface respectively?

The user interface defines how the system will interact with users. User interface is everything end user comes into contact with while using the system

The system interfaces define how systems exchange information with other systems. Analyst designs system interfaces separate from user interfaces

4. List at least 4 typical menu types and discuss their advantages and disadvantages. Pp339

Tool bars

Drop down menus

Pop-up menus

Image maps

5. Write an SQL statement to create a view about the hotel numbers and hotel names.

CREATE VIEW hotelPrice AS

SELECT hotelNo, hotelName

FROM hotel

6. Write an SQL statement to insert the staff numbers and names into the guest table.

INSERT INTO guest (guestNo,guestName,email)

SELECT staffNo,staffName,email

FROM staff;

(2 marks) Practices:

1. (1 mark) SAD – User Interface Design. Design a user interface for the final project (i.e., the MICT course project).

Note: This user interface design will not be included in the final project. However, it is important to gain some experience in user interface design as this is a key step to the success of an IT project.

1. (1 mark) SQL – other statements in basic SQL
   1. Create a view to check the hotel name, room number and the guest names who booked the room;

1. Select everything from the view created above;
2. Drop the above view;
3. Create a view to check the hotel number and hotel names and the number of rooms they have.
4. Create a view to see the pair of room types and prices.
5. Create a view to see the distinct pair of room types and prices.
6. Create an index of room types and prices on the room table;
7. Drop the above index;
8. Create an index of the guest names;

CREATE INDEX indexName

ON tableName (columnList);

1. Create a new table including hotel information (i.e., hotel names and numbers) from the room table;
2. Backup the room table;
3. Insert the staff numbers and names as the guest numbers and names into the guest table. If not successful, why?

Tutorial 9(1 mark) Questions:

1. What is functional dependency, partial functional dependency and transitive functional dependency?

• Functional dependency describes relationship between attributes. Functional dependency is a property of the meaning or semantics of the attributes in a relation.For example, if A and B are sets of attributes of relation R, B is functionally dependent on A (denoted A ® B), if each value of A in R is associated with exactly one value of B in R.
• Suppose that A determines B. Otherwise, A partially determines B (or B is partially dependent on A)

• roomNo & hotelNo trasitively determines price via type because roomNo & hotelNo determines type and type determines price

2. Why normalization is important for database design?

Normalization is a process to eliminate the flaws of a database with bad design. A poorly designed database is inconsistent and create issues while adding, deleting or updating information.

3. What are the two typical structure arrangements?

• Program Design- From logic DFD to physical DFD

• Data Storage Design- From ERD to GRD and beyond

4. Write a pseudocode for the sorting algorithm (i.e., put a list of numbers into an order).

(2 marks) Practices:

1. (1 mark) SAD-DB – Logical Database Design. Convert your Entity Relationship Diagram to Global Relation Diagram.

Note: This user interface design will be included in the final project. Your final database should be one-to-one corresponding to the relations in your GRD. You can use the ERD suggested by the tutor. However, you are strongly encouraged to use your own ERD.

1. (1 mark) SQL – advanced SQL
   1. Declare a cursor of the room table containing all hotel numbers and hotel names;

1. Open the above cursor and use it to generate the hotel numbers and names one by one;
2. Release the above cursor;
3. Create a stored procedure to update the room prices in hotel Hilton by 6% and in other hotels by 4%.
4. Use the above procedure to update the room prices.