Imagine that you are a Database Programmer working in a software company. Your company has assigned the programmers to choose from several projects for their clients. Choose any one project and prepare the appropriate requirements to build the system using SQL. (55 marks)
1. COLLEGE DATABASE: A college contains many departments. Each department can offer any number of courses. Many instructors can work in a department, but an instructor can work only in one department. For each department, there is a head, and an instructor can be head of only one department. Each instructor can take any number of courses, and a course can be taken by only one instructor. A student can enroll for any number of courses and each course can have any number of students.
2. LIBRARY MANAGEMENT SYSTEM: A student and faculty can issue books. The number of books a student and teacher can be issued will vary. In addition, the number of days will be distinct in the case of students and teachers for issue of any book. Each book will have different ID. Each book of the same name and same author (but the number of copies) will have different ID. Entry of all the books will be done, as to who will issue that book and when and should include the duration. Detail of Fine (when the book is not returned at a time) is also stored.
3. PAYROLL MANAGEMENT SYSTEM DATABASE PROJECT: There will entry (Unique ID) of all the employee of any Organization. According to the date of joining and date up to which salary is created, Number of days will be entered. Basic pay will be defined according to the post of employee and department. Then component like DA, HRA, medical allowance, Arrears will be added, and Charges of car, Security, GOSI and other will be deducted. The number of leaves taken by the employee should also be included.
REQUIREMENTS
1. Design the database for any one given Scenario with the help of SQL Developer using appropriate DDL and DML commands. (20 marks)
2. Construct an Entity Relationship diagram describing the relationships that exist on the database. (10 marks)
3. Create at least 5 relevant queries to display details for the above database (10 marks- 2 marks for each correct query)
4. Technical Report: A documentation of the project has to be submitted with the various commands used in DDL and DML applicable to this project. Snapshots of the tables, output of the commands and queries with clear explanation wherever needed. (5 marks)
5. A. PRESENTATION AND B. TEAMWORK(10 MARKS)
RUBRICS:
DATABASE CREATION
Criteria Excellent (5) Good (4-3) Satisfactory (2-1) Unsatisfactory (0) Rating
Information is Most information is Some information There is no clear plan
organized in a organized in a is logically for the organization
Relevance of clear, logical way. It clear, logical way. sequenced. of information.
information in is easy to anticipate Some information
the database the type of material seems out of place.
that might be in
other tables.
All tables are All tables are All tables are Few tables are only
Tables present in the database with a present in the database with present in the database. created.
minimum of 5 fields required fields. The
and a total of 10 number of records
records has not been met.
Database shows Database shows Database shows Database is a rehash
considerable some originality an attempt at of other people’s
Originality originality and inventiveness. The and inventiveness. The content and originality and inventiveness. ideas and shows very little attempt at
content and ideas ideas are presented original thought.
are presented in a in an interesting
unique and way.
interesting way.
Relationships Adequately Adequately Student was not Some relationships
demonstrates demonstrates able to apply have only been
understanding of understanding of foreign keys applied and is not
foreign keys, foreign keys and accurately, did not complete.
primary keys, and primary keys, but follow relationship
relationship does not follow protocol.
protocol by creating relationship
a relationship table protocol with one of
with foreign keys the Directories.
identified.
ENTITY- RELATIONSHIP DIAGRAM
Criteria Excellent (5) Good (4-3) Satisfactory (2-1) Unsatisfactory (0) Rating
Logically designed Exhibited adequate Exhibited a partial Exhibited minimal
the database understanding of understanding of understanding of
Overall Crow’s
Foot Model design exhibiting a firm understanding of Crow’s Foot Crow’s Foot database design and manipulation. Crow’s Foot database design and manipulation. Crows’s Foot database design and manipulation
notation
Implementation Completely populated tables with correct data elements reflecting the ERD design Populated tables with majority of data elements outlined in the ERD design Populated tables with minimal data elements defined in project the ERD design Populated tables with minimal data elements defined in project the ERD design whic
