Databases | SQL | Oracle | MS Access Answers

Polly Pipe is a water sports provider and installer based in Braintree, England. They need to design and implement a database. Polly Pipe is focused in placing aquariums at business customers. Customers can request several installations, but each installation is tailor-made for a specific customer. Facilities are classified by type. One or more employees are assigned to each facility. Because these facilities are often very large, they can include carpenters and masons as well as water installers. The facilities use equipment such as aquariums, air pumps and thermostats. There can be multiple computers in a facility.

Draw the Use Case Diagram and Class Diagram for this scenario.

Polly Pipe is a water sports provider and installer based in Braintree, England. They need to design and implement a database. Polly Pipe is focused in placing aquariums at business customers. Customers can request several installations, but each installation is tailor-made for a specific customer. Facilities are classified by type. One or more employees are assigned to each facility. Because these facilities are often very large, they can include carpenters and masons as well as water installers. The facilities use equipment such as aquariums, air pumps and thermostats. There can be multiple computers in a facility.

Produce a technical documentation and a user guide for the developed database system. Suitable diagrams (Use case diagram, class diagram, flow charts, DFD level 0 and 1) should be included in the technical documentation to show data movement in the system. Assess the developed database by suggesting future enhancements to ensure the effectiveness of the system. 

Get the number of male and female premium users in the platform.

As mentioned above, We need to count the users who has the premium membership, but you are trying to sum instead of count(). And also in the WHERE clause, we need to check premium users have premium_membership value as 1 , and the value 0 is considered as non-premium user.

Table: USER

user_id name gender age country premium_membership

2000 John White M 63 AUSTRALIA 1

2001 John Andrews M 67 AUSTRALIA 1

2002 April Robinson F 60 SRILANKA 1

2003 Kathy Ryan F 81 SRILANKA 0

2004 Megan Bradshaw F 53 AUSTRALIA 1

2005 Melissa Sullivan F 45 BANGLADESH 1

there is still having lot of data in the table, but I just gave the limited data for example to get the solution related to question and the expected output format should be as follows:

Table: gender total_users

F ...

M ...

/*7. Show any employee who is not a manager, but earns more than any manager in

the employees table. Show first name, last name, job id, and salary. Sort the result 

by salary.*/

What are some ways of writing is query?

Identify the user and system requirements to design a database for the above scenario and design a relational database system using conceptual design (ER Model) by including identifiers (primary Key) of entities and cardinalities, participations of relationships. Convert the ER Model into logical database design using relational database model including primary keys foreign keys and referential Integrities. It should contain at least five interrelated tables.

https://docs.google.com/document/d/16o4eJA_nK8IyUxHcPhavXGYliqWpnHujbm1JDnZ3Z5M/edit?usp=sharing

Open the above link and Identify at least THREE Non Functional Requirements according to it.

https://docs.google.com/document/d/16o4eJA_nK8IyUxHcPhavXGYliqWpnHujbm1JDnZ3Z5M/edit?usp=sharing

Open the above link and Identify at least FIVE Functional Requirements according to it. 

https://docs.google.com/document/d/16o4eJA_nK8IyUxHcPhavXGYliqWpnHujbm1JDnZ3Z5M/edit?usp=sharing

Click on the above link and Identify User Characteristics or types of users from the above scenario.

a) Create the above two relations and impose the following constraints on that (10 marks) Attribute name Data type Constraints ID Number (7) Primary key Name Varchar (15) Not Null Deptname Char(3) Not Null Foreign key for instructor relation Primary key for department relation Budget Number (7) NOT NUll

b) Insert the following tuples to the department relation “MGBlock, CSE, 50L” & “SJTAnnex, CHE, 16L” – Present the output for each insertion. (5 marks) c) Form a new relation from with the following attributes – building, department, budget, number of instructors in each department. Name the new relation as Dept_Instcount - (10 marks) d) Update the tuple of Dept_Instcount – if instructor count is more than 2, increase the budget to 30L. (10 marks) e) Implement question – (b) as a trigger (10 marks) 

1. Insert a node at beginning

2. Insert a node at end

3. Delete a node from beginning

4. Delete a node from end

5. Count number of nodes in Circular linked

list