Question 1: 10pts Design a database for an online job board and hiring system. The system should keep track of Companies, JobPostings, Applicants, Applications, Interviews, and Skills. Whether these are represented as entity sets or relationships is up to you.
The following information should be represented in your design:
- Companies post job openings. A company may have many active job postings.
- Each job posting includes information such as a title, location, salary range, and employment type (e.g., full-time, part-time, internship, contract).
- Applicants may apply for multiple jobs, and each job may receive applications from many applicants.
- An application should track when the applicant applied and its current status (e.g., submitted, under review, interview, rejected, offered).
- Applicants have skills, and the same skill may be held by many applicants.
- Job postings may require or prefer multiple skills.
- Applicants selected for an interview may have one or more interviews associated with their application. Interviews should track information such as the scheduled time, interview type, and outcome.
- An applicant should not be able to submit more than one active application for the same job posting.
Draw the E/R Diagram for this database. Be sure to include keys, constraints, cardinalities, and relationship arity where appropriate.
You may add attributes where necessary, but your diagram should represent all of the requirements above.
Question 2: 5pts Many companies allow current employees to refer applicants for open positions. Extend your diagram from Question 1 by adding exactly one new Entity Set called Referral and one new Relationship connecting it to your existing design.
A referral should represent an employee or recruiter recommending an applicant for a particular job posting. Your Referral entity should include an appropriate key and at least two additional attributes, such as the referral date, referral status, or a short recommendation note.
Draw the new entity set and relationship on your diagram and, in a few sentences:
- explain what your new relationship represents,
- identify its cardinality and participation constraints, and
- explain how referrals fit into the hiring process represented by your original database.
Question 3: 5pts One way to represent “Students” and the grades they get in “Courses” is to use entity sets corresponding to “Students”, “Courses”, and “Enrollments”. Enrollments connect Students and Courses. Enrollments can be used to represent not only the fact that a student is taking a certain course, but the grade of the student in the course. Draw an E/R Diagram for this situation, indicating entity sets and the keys for the entity sets.
Question 4: 5pts
Consider again the Job Board and Hiring database from Questions 1 and 2. Job postings are often broken down into different types, such as FullTimePosition, InternshipPosition, ContractPosition, and ExecutivePosition. Reconsider your design and this time represent these different kinds of positions using sub-entity sets of JobPostings.
Hint: A sub-entity should have a reason to exist. It should have at least one attribute or relationship that is not inherited from its parent.
FAQ: You do not need to redraw your entire diagram. You may show only the portions of the diagram that you changed or added.
Question 5: 5pts We only had time to talk about the standard (Chen) notation for ER Diagrams, but there exist many other ways to create ER Diagrams. Some are more expressive than others, but they basically do the same thing. Use your Google skills and read up on Crows-foot notation. Convert your answer to Q4 into Crows Foot notation.
FAQ: redraw the whole diagram including the changes from Q4
Question 6: 10pts Now let’s consider a banking domain. For the relations Accounts and Customers here:
| acctNo | type | balance |
|---|---|---|
| 12345 | savings | 12000 |
| 23456 | checking | 1000 |
| 34567 | savings | 25 |
| firstName | lastName | ID | account |
|---|---|---|---|
| Robbie | Banks | 901222 | 12345 |
| Linda | Hand | 805333 | 12345 |
| Linda | Hand | 805333 | 23456 |
6a: Suppose string attributes (type, firstName, lastName) are all defined as CHAR(20). How much disk space will the tuples in each relation occupy? Show your work.
6b: Suppose string attributes are all defined as VARCHAR(200). How much disk space will the tuples in each relation occupy? Show your work.
6c: When might we choose CHAR instead of VARCHAR? Why?
Question 7: 5pts Here we introduce a relational database schema for a University Career Fair. The database consists of four relations:
- Company (company_id, name, industry)
- Booth (booth_number, building, floor, size)
- Representative (rep_id, name, title, company_id)
- Session (session_id, topic, room, start_time)
The Company relation stores organizations participating in the career fair. Each company has a unique company_id, along with its name and industry. The Booth relation stores information about booth spaces used during the fair. Each booth has a unique booth_number, along with its building, floor, and size. The Representative relation stores company representatives attending the fair. Each representative has a unique rep_id, along with their name, job title, and the company_id of the company they represent. The Session relation stores scheduled career-fair sessions such as employer presentations, résumé workshops, or information sessions. Each session has a unique session_id, along with its topic, room, and starting time.
Your task: Write SQL CREATE TABLE declarations for each relation. Determine and declare the primary key for each relation.
Question 8: 5pts (from Garcia-Molina, p165) Convert this E/R Diagram below to a relational database schema.
Question 9: 5pts (from Garcia-Molina, p165) This E/R Diagram below represents ships. Ships are said to be sisters if they were designed from the same plans. Convert this diagram to a relational database schema.
Question 10: 5pts Convert your answer to Q4 to a relational database schema.
FAQ — the whole schema, not just the new things from Q4.
Some exercises are from Garcia-Molina, Ullman, Widom. Database Systems. 2nd Edition.
This is an individual assignment. Discussion is allowed, but students may not write submitted solutions in the presence of a group.
Cite your sources.
Submissions must be uploaded to GradeScope and marked by the due date.