Design the relational schema, indicating primary and foreign keys, and any referential integrity or other constraints you believe are necessary. For instance, a team cannot play itself! Also give the Relation Algebra for queries 1. and 2. below. Again, there are problems for plain text files: Greek characters for л use PROJ, for σ use SEL, for use JOIN, for τ use SORT To represent tree structure use (parentheses), or use to form the tree Example PROJ (code, title, credits) JOIN(name=prof) SEL (prof='Jensen') COURSE PROF Text Due date given on course webpage. Also write SQL queries for the following: (If not possible, reconsider your design) 1. A list of names and phone numbers of all the players assigned to the Gazelles. 2. The schedule for the Jaguars, ordered by date. (note: the DATE type in SQL can be ordered) 3. The Tigers' head coach and assistants, their names and phone numbers (one or 2 queries). 4. The Lions have today beaten the home team Tigers by a score of 3-2. Update the database accordingly. 5. A list of all games won by the Lions, with the score, team names, and date. 6. The number of players on each team, and the birthdates of the oldest player and the youngest player (on each team). I expect a single query, using GROUP BY 7. (optional) Add to the last query also the average age, in days, of the players on each team. Note that current_date - birthdate is the age of a person in days, an integer that can be averaged.
Design the relational schema, indicating primary and foreign keys, and any referential integrity or other constraints you believe are necessary. For instance, a team cannot play itself! Also give the Relation Algebra for queries 1. and 2. below. Again, there are problems for plain text files: Greek characters for л use PROJ, for σ use SEL, for use JOIN, for τ use SORT To represent tree structure use (parentheses), or use to form the tree Example PROJ (code, title, credits) JOIN(name=prof) SEL (prof='Jensen') COURSE PROF Text Due date given on course webpage. Also write SQL queries for the following: (If not possible, reconsider your design) 1. A list of names and phone numbers of all the players assigned to the Gazelles. 2. The schedule for the Jaguars, ordered by date. (note: the DATE type in SQL can be ordered) 3. The Tigers' head coach and assistants, their names and phone numbers (one or 2 queries). 4. The Lions have today beaten the home team Tigers by a score of 3-2. Update the database accordingly. 5. A list of all games won by the Lions, with the score, team names, and date. 6. The number of players on each team, and the birthdates of the oldest player and the youngest player (on each team). I expect a single query, using GROUP BY 7. (optional) Add to the last query also the average age, in days, of the players on each team. Note that current_date - birthdate is the age of a person in days, an integer that can be averaged.
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
Related questions
Question
I need experts answer please. Thank you
Transcribed Image Text:Design the relational schema, indicating primary and foreign keys, and any referential integrity or other constraints you believe are necessary. For instance, a team cannot play itself!
Also give the Relation Algebra for queries 1. and 2. below. Again, there are problems for plain text files:
Greek characters
for л use PROJ, for σ use SEL, for use JOIN, for τ use SORT
To represent tree structure
use (parentheses), or
use to form the tree
Example
PROJ (code, title, credits)
JOIN(name=prof)
SEL (prof='Jensen')
COURSE
PROF
Text
Due date given on course webpage.
Also write SQL queries for the following: (If not possible, reconsider your design)
1. A list of names and phone numbers of all the players assigned to the Gazelles.
2. The schedule for the Jaguars, ordered by date. (note: the DATE type in SQL can be ordered)
3. The Tigers' head coach and assistants, their names and phone numbers (one or 2 queries).
4. The Lions have today beaten the home team Tigers by a score of 3-2. Update the database accordingly.
5. A list of all games won by the Lions, with the score, team names, and date.
6. The number of players on each team, and the birthdates of the oldest player and the youngest player (on each team). I expect a single query, using GROUP BY
7. (optional) Add to the last query also the average age, in days, of the players on each team. Note that current_date - birthdate is the age of a person in days, an integer that can be averaged.
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Step by step
Solved in 2 steps
Recommended textbooks for you
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education