2. A college needs a database that supports its placement office to help college students finding out potential employers. The primary purpose of the database is to schedule interviews and facilitate searches by students and companies. The requirements are the followings:
• Student data include a unique identifier, a name,
phone number, an email address, a web address, a major, a minor,
and a GPA.
• The placement office maintains a standard list of
positions based on the Labor Department's list of occupations.
Position data include a unique position identifier and a position
description.
• Company data include a unique company identifier, a
company name, and a list of positions and interviewers. Each
company must map its positions into the position list maintained by
the placement office. For each available position, the company
lists the cities in which positions are available.
• Interviewer data include a unique interviewer
identifier, a name, a phone, an email address, and a web address.
Each interviewer works for one company.
• An interview includes a unique interview identifier,
a date, a time, a location (building and room), an interviewer, and
a student.
• An interview may be setup without knowing the
interviewer
• An interview is setup with the student
Let us first draw the Er diagram using following steps
• Make an Entity set Position with attributes pos_identifier and
a pos_description.
• Entity set Company with attributes com_id, com_name, the relation
ship between company and position will have attributes
location
• Entity set Interviewer interviewer_id,name, phone, email ,
webaddress. A relation ship between interviwer and company will be
many to one
• An interview includes a unique interview identifier, a date, a time, location (wiil be composite attribute including
building and room), an interviewer, and a student.will be shown
as binary relationship between student and interviewer
• An interview may be setup without knowing the
interviewer
• An interview is setup with the student
the Er diagram will be as below
The final detailed database would be as below
NOTE: THE DATA TYPES AND SIZE OF EACH ATTRIBUTES CAN BE CHOSEN AS REQUIREMENT
Table: STUDENT
ATTRIBUTE NAME | DESCRIPTION |
SID | PRIMARY KEY |
S_NAME | |
S_PHONE | |
S_EMAIL | |
S_WEBADRESS | |
MAJOR | |
MINOR | |
GPA |
Table: INTERVIWER
ATTRIBUTE NAME | DESCRIPTION |
INTERVIEWER_ID | PRIMARY KEY |
NAME | |
PHONE | |
WEBADRESS | |
COMP_ID | FOREIGN KEY REFERENCES COM_ID OF COMPANY TABLE |
Table: POSITIONS
ATTRIBUTE NAME | DESCRIPTION |
POS_ID | PRIMARY KEY |
DESCRIPTION |
Table: COMPANY
ATTRIBUTE NAME | DESCRIPTION |
COM_ID | PRIMARY KEY |
C_NAME |
Table: VACANCY
ATTRIBUTE NAME | DESCRIPTION |
COMP_ID | PRIMARY KEY{COMP_ID,POS_ID}, COMP_ID FOREIGN KEY RFERENCING COM_ID OF COMPANY AND POS_ID FOREIGN KEY REFERENCING POS_ID OF POSITION |
POS_ID | |
CITY |
Table: INTERVIEW
ATTRIBUTE NAME | DESCRIPTION |
INTERVIWER_ID | PRIMARY KEY |
DATE | |
TIME | |
ROOM | |
BUILDING | |
S_ID | FOREIGN KEY REFERENCING S_ID OF STUDENT |
INTERVIEWR_ID | FOREIGN KEY REFERENCING INTERVIEWER_ID OF INTERVIEWR |
2. A college needs a database that supports its placement office to help college students finding...
Develop a conceptual ER diagram for the above database description, using appropriate syntaxes, and showing cardinalities and key attributes. Consider the partial description for a UNIVERSITY database below: A university is organized into colleges, and each college has a unique name, a main office and phone, and a particular faculty member who is dean of the college. Each college administers a number of academic departments. Each department has a unique name, a unique code number, a main office and phone,...
Programming Assignment This is a database course using Open Office Database. Assume that you have been given the task of creating a system for a library to keep track of their books, the borrowers of the books, and the books that are currently lent out. Your first step will be to create the relations necessary for this system. Book will need information such as a unique identifier for each book, title, author, ISBN number, date of publication, cost. Borrower will...
Suppose a local college has tasked you to develop a database that will keep track of students and the courses that they have taken. In addition to tracking the students and courses, the client wants the database to keep track of the instructors teaching each of the courses. Database Design Diagram Use any drawing package or any other application you know such as Visio, Word, PowerPoint, or another tool to create the Database Design Diagram. Otherwise, you may draw the...
Consider the database schema of college database. Students have a major department and take classes where faculty can be met. Student (cwid int, name, text, age int, majorDept text) Department (name text, chair text) Faculty (name text, deptName text) Enrollment (facName text, studID int) Note that the underlined attributes are primary keys. studID, chair, deptName of Faculty, facName and majorDept of Student are foreign keys to cwid, name of Faculty, name of Department, name of Faculty and also name of...
Question 1: University Database Consider the following set of requirements for a UNIVERSITY database that is used to keep track of students' transcripts. The university keeps track of each student's name, student number, social security number current address and phone, permanent address and phone, birth date, sex, class (freshman, sophomore,., graduate), major department, minor department (if any), and degree program (B.A., B.S.,..., Ph.D.). Some user applications need to refer to the city, state, and zip of the student's permanent address,...
The University Accommodation Office Case Study The director of the University Accommodation Office requires you to design a database to assist with the administration of the office. The requirements collection and analysis phase of the database design process has provided the following data requirements specification for the University Accommodation Office database followed by examples of query transactions that should be supported by the database. Data Requirements Students(entity) The data stored for each full-time student includes: the banner number, name (first...
Create Datasets for the ABC University Accommodation Office using the information below.This is a list of all the datasets and data attributes that the Office needs to function. For example, a STUDENT dataset containing StudentIDNumber, StudentFirstName, etc Scenario - ABC University Accommodation Office (Student Housing) The director of the ABC University Accommodation Office requires you to design a database to assist with the administration of the office and the renting of residences to students. The requirements collection and analysis phase...
I want ER diagram Australian Institute of Music is a music college across the Australia. It provides wide ranges of courses related to music. The top-level management of the college has recently decided to computerise the existing paper-based data to a database in order to keep record of all staff, students, courses, etc. Such database will facilitate saving, restoring, and reporting whenever required and reduced paper consumption in the college. The following describes information that is required to save in...
Problem 1 Consider the following set of requirements for a university database that is used to keep track of students' transcripts. (a) The university keeps track of each student's name, student number, social security number, current address and phone, permanent address and phone, birthdate, sex, class (freshman, sophomore, -, graduate), major department, minor department (if any), and degree program (B.A., B.S., ...., Ph.D.). Some user applications need to refer to the city, state, and zip of the student's permanent address,...
Assume that you are working with a hospital and this hospital needs a software system to track its patients’ information. Your role in this software development is to design the database. There are many aspects of such a hospital software system to develop. However, in this assignment, you will only address interactions between doctors and patients. Your first step will be to create the relations necessary for this system and identify and describe the constraints that would be appropriate for...