Design and develop a database of your choice that includes at least: Five entiti

Question

Design and develop a database of your choice that includes at least: Five entities. Structural Constraints a. At least two of the three cardinality constraints. (1: 1, 1: N, N: M) b. Both participation constraints (partial or full) One relation has relational attributes. A superclass-subclass relationships-one disjoint relationship or one overlap relationship. (May use both) Other notes: Do not create too many attributes for the entities. Three to four attributes for each entity will be enough. Make sure to include a primary key for each entity. You can create a db related to your work or you can be creative, the topic is not as important as the process. Please do not go overboard: if you plan on implementing this at some later date, use this as a starting draft, I do not want a diagram with 20 entities or entities with 10 or more attributes. Keep it as close to the minimums as possible. Write all of the specifications for your database (Structured English). Follow the format in the ER book. Draw the ER diagram according to your specifications. Map the ER diagram to a relational database, (schema diagram) Write the SQL to create the database, (create table command) a. Include attributes types. b. Include constraint information (Primary key, not null. Foreign key etc) (See sq1 chpt 10). Write 5 "canned'' queries for your schema.(oracle sq1 only) The query should have at least one join. A canned query is a query that represents the most common queries run on the database. Many times these query are coded into an application so "help desk" or other "casual users' can run them and get results. You don't need to actually implement this in a DBMS.

Explanation / Answer

uNIVERSITY sySTEM

there following entitiy

Student

COurse

Faculty

Offering

Enrollment

Student can enroll in the course faculty will conduct the course

offering define for which course which faculty is assigned for how long

Enrollment is relation between student course and offering

part 5 ) some of the queries

Oracle Examples in Module 4

Lesson 2

1.
SELECT * FROM Faculty;

2.
-- Need hyphens in constant
SELECT *
FROM Faculty
WHERE FacNo = '543-21-0987';

3.
SELECT FacFirstName, FacLastName, FacSalary
FROM Faculty;

4.
SELECT FacFirstName, FacLastName, FacSalary
FROM Faculty
WHERE FacSalary > 65000 AND FacRank = 'PROF';

5.
SELECT FacFirstName, FacLastName, FacCity,
FacSalary*1.1 AS IncreasedSalary,
FacHireDate
FROM Faculty
WHERE (date_format(FacHireDate, '%Y')) > 2001;

6.
SELECT *
FROM Offering
WHERE CourseNo LIKE 'IS%';

7.
SELECT FacFirstName, FacLastName, FacHireDate
FROM Faculty
WHERE FacHireDate BETWEEN '2004-01-01'

AND '2005-12-31';

8.
SELECT OfferNo, CourseNo
FROM Offering
WHERE FacNo IS NULL AND OffTerm = 'SUMMER'
AND OffYear = 2013;

9.
SELECT OfferNo, CourseNo, FacNo
FROM Offering
WHERE (OffTerm = 'FALL' AND OffYear = 2012)
OR (OffTerm = 'WINTER' AND OffYear = 2013);

Lesson 4

1.
SELECT OfferNo, CourseNo, FacFirstName,
FacLastName
FROM Offering, Faculty
WHERE OffTerm = 'FALL' AND OffYear = 2012
AND FacRank = 'ASST' AND CourseNo LIKE 'IS%'
AND Faculty.FacNo = Offering.FacNo;

2.
SELECT OfferNo, CourseNo, FacFirstName, FacLastName
FROM Offering INNER JOIN Faculty
ON Faculty.FacNo = Offering.FacNo
WHERE OffTerm = 'FALL' AND OffYear = 2012
AND FacRank = 'ASST' AND CourseNo LIKE 'IS%';

3.
SELECT OfferNo, Offering.CourseNo, OffDays,
CrsUnits, OffLocation, OffTime
FROM Faculty, Course, Offering
WHERE Faculty.FacNo = Offering.FacNo
AND Offering.CourseNo = Course.CourseNo   
AND OffYear = 2012 AND OffTerm = 'FALL'
AND FacFirstName = 'LEONARD'
AND FacLastName = 'VINCE';

4.
SELECT OfferNo, Offering.CourseNo, OffDays,
CrsUnits, OffLocation, OffTime
FROM Offering INNER JOIN Course
ON Offering.CourseNo = Course.CourseNo
INNER JOIN Faculty
ON Offering.FacNo = Faculty.FacNo
WHERE OffYear = 2012 AND OffTerm = 'FALL'
AND FacFirstName = 'LEONARD'
AND FacLastName = 'VINCE';

Lesson 5

1.
SELECT FacNo, FacRank, FacSalary
FROM Faculty
ORDER BY FacRank;

2.
SELECT FacRank,
AVG(FacSalary) AS AvgSalary
FROM Faculty
GROUP BY FacRank
ORDER BY FacRank;

3.
SELECT StdMajor, AVG(StdGPA) AS AvgGpa
FROM Student
WHERE StdClass IN ('JR', 'SR')
GROUP BY StdMajor;

4.
SELECT StdMajor, AVG(StdGPA) AS AvgGpa
FROM Student
WHERE StdClass IN ('JR', 'SR')
GROUP BY StdMajor
HAVING AVG(StdGPA) > 3.1;

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.