Databases | SQL | Oracle | MS Access Answers

Dineo, being ever curious and wanting to learn more about anything and everything, decided to try her hand at creating a UML ERD for a relational database. You made it look so easy, so she was confident that she could do it too. So, she wrote some new rules about campus life and created the below ERD based on some web pages that she consulted.

Explain the five components of a DBMS and why Dineo would need each of the (10) components for her website.

Dineo has given what she wants to display on the website a lot of thought. And she has come up with quite a comprehensive set of business rules.

  Launch Data To Be Displayed

  Draw an Entity Relationship Diagram (ERD) using Unified Modelling Language (UML) notation, representing these business rules. Your design should be at the logical level – include surrogate primary key and foreign key fields and remember to remove any many-to-many relationships.

After carefully studying the infographic, Dineo now needs to choose whether to use a relational database or a NoSQL database. She says that she needs to store the following information:

• She needs to know who built the vehicle, where it was launched from, and when for each launch. For crewed missions, she needs to know who the astronauts were on board.

• Each launch also has multimedia that should be included – a text description, photos, maybe even videos.

Explain to her which type of database (relational or NoSQL) you would recommend, and why, for each of the following types of data.

  Q.2.1 Launch data.

Q.2.2 Multimedia related to the launch.

Students as a group are associated with multiple faculty members, and faculty members in turn are associated with multiple-students. For this scenario, the cardinality between the student entity and the faculty entity is....?

one to many

Get all the videos that are released in the year 2018.

Note:

Sort the output in the descending order of

published_datetime and then in the alphabetical order of name Expected Output Format;

video_id name duration_in_secs no_of_views............

Get the top 10 most viewed videos till date.

Note:

Sort the output in the descending order of

no_of_views and published_datetime Expected Output Format;

name channel_id no_of_views.........

Get videos from TEDx channel (id=353) with more than 50 thousand views.

Note:

Sort the output in the descending order of

no_of_views and in the ascending order of videonameExpected Output Format

video_id name duration_in_secs no_of_views

• Create a MySQL database with a minimum of 5 tables. Use the DDL commands. The tables must be related using the primary and foreign keys.
• Add at least 5 records per table using the Insert command.
• Delete 1 record in any of the tables.
• Edit 1 record in any of the tables.
• Write and execute at least 5 queries to retrieve records using the Select command. Select limited columns, filter records using where, sort records using order by, and link two or more tables using an inner join.

• Create a MySQL database with a minimum of 3 tables. Use the DDL commands. The tables must be related using the primary and foreign keys.
• Add at least 5 records per table using the Insert command.
• Delete 1 record in any of the tables.
• Edit 1 record in any of the tables.
• Write and execute at least 5 queries to retrieve records using the Select command. Select limited columns, filter records using where, sort records using order by, and link two or more tables using an inner join.