Assignment
Answer all questions
1. Answer the following database-relatedquestions:
i) What is three-schema architecture for database systems? Explain what advantages NoSQL approaches have over traditional DBMS solutions.
ii) Draw an ERD that includes two entities: JUDGE with attributes (id, name, jurisdiction) and CASE with attributes (case_id, prosecutor, jurisdiction). The relationship between them is PRESIDES_OVER. A judge may preside over cases and can have a maximum of 20 cases; each case must be assigned to exactly one judge. Please show cardinality ratios using both Chen's notation and IE notation respectively. Note: You can just type text on two ERDs to show cardinality ratios.
iii) Create an EERD using a total category for a PROPERTY entity type that may be a LOT or a BUILDING entity type.
In a generalization process if you have a LOT and a BUILDING, then how will you come up with your EERD? (Note: This is a different design) (2 Points). Please explain your design.
iv) From the COMPANY database discussed in the class, what's the operation type specified in the Relational Algebra to retrieve the names of employees who work on all the projects that ‘John Smith' works on.
If one relation has M tuples and N attributes and the other has J tuples and K attributes, what are the number of tuples and attributes for the Cartesian product for these two relations?
If one relation has M1 attributes and the other has M2 attributes with two attributes specified as a join condition on each relation, how many attributes for the EQUIJOIN and how many attributes for the NATURAL JOIN (assuming M1> 2 and M2> 2)?
v) What are relational constraints defined in a relational database schema? Which relational constraint defines a relationship between two relations?
vi) Specify the following queries on the COMPANY relational database schema shown in Figure 5.5 (page 161, 7th edition), using the relational operators. Also show the result of each query as it would apply to the database state of Figure 5.6 (page 162, 7th edition).
(a) Retrieve the names of employees in department 5 who work more than 10 hours per week on the 'ProductX' project.
(b) Find the names of employees that are directly supervised by 'Franklin Wong'.
(Note: If you use the 6th edition, then the problem is:
Specify the following queries on the COMPANY relational database schema shown in Figure 3.5 (page 71, 6th edition), using the relational operators. Also show the result of each query as it would apply to the database state of Figure 3.6 (page 72, 6th edition).
(a) Retrieve the names of employees in department 5 who work more than 10 hours per week on the 'ProductX' project.
(b) Find the names of employees that are directly supervised by 'Franklin Wong')
2. Design a database with ERD that keeps track of the teams and games of a baseball league.
Requirements:
• A league has many teams.
• A team has at least 12 players and up to 16 players with last names, first names, and SSNs.
• A team has at least one coach and up to three coaches with last names and first names, titles and their availabilities.
• A player may actively play for one team at a time; however, the player could have played for other team(s) before. It is necessary to track the team(s) a player has played. It is possible that a player may not play for any team.
• Each team plays a game with other teams in the league each year.
• A game consists of a home team and a visiting (away) team; it is required to know which two teams play a game.
• A game has a date, time and location to play.
• A game also includes the final scores and errors for both home and way teams.
• Not all of players in a team will participate in each game, you need to know who are available and whether a player in a team plays or not.
• A game normally consists of nine innings, but it may have extra innings if the scores are tie and require an extra inning(s) to decide which team wins the game.
• There are a score, number of hits, and number of errors for each team in each inning.
• If a player plays a game, the player's performance is recorded by a coach at the end of each game with a scale of 1 (worst) to 5 (best).
Draw the E/R diagram for the database. This E/R diagram should include entities, proper attributes, relationships and proper cardinality ratios. You need to specify primary keys and foreign keys for all entities. You also need to specify the rationale for your design.(25 points)
3. A nationwide health club provides a broad range of fitness services for its members at several major US locations at a discount. A member may be an individual member or a company member that allows the company's employees to use the services. An individual member will pay his/her own fees for services, a company employee will be paid for by his/her company. The payment will be charged by credit cards.
The health club offers the following categories of services:
• Physical Therapy - Massage, Sports medicine through a network of physicians
• Group Fitness - aquatics, aerobics, weight classes
• Personal Training - weight loss, toning, weight lifting, cardio
• Health Assessments - BMI, cardiovascular, muscle tone
Use Enhanced ER Diagram (EERD) to design a database to track a member to use their services. The EERD must include some EERD notations such as superclass/subclass, categories, and other constraints. You only need to include key attributes for the entities (such as Physical Therapy and so on) and relationships to reduce complexity of your design. You don't need to identify attributes associated with Physical_Therapy and other entities.
State clearly any assumptions you make and your design rationale. You may use any graphical tools to draw your EERD or you can use hand-writing to clearly show your EERD.
4. Convert the following ERD in Chen's notation to an ERD in IE notation. All entities, attributes, and relationships are artificial.
You need to clearly show strong and weak entities, foreign key migrations, total participations and partial participations, and cardinality ratios.
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also includes a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.
Attachment:- Database-Assignment.rar