Assignment: DBMS PROJECT
Topic: Payroll management system
Intersession 1: Database Design Project Proposal
Review the Final Project Description and begin to plan your database design. The first step to any database design is to determine its purpose and to define how it will be used. This week, you must submit your Database Design Project Proposal that explains what kind of database you will design, the type of information your database will contain, and the type of information output for your database.
Use the following questions to guide you as you plan the needs and features for your database.
• What is the purpose of the database? Why is it needed? What should it do?
• Who are the users and what are their information needs?
• What are the questions that the system should answer?
• What type of input data is going to be available to the database?
• What kind of information should be stored in the database?
Then, write a one-page, proposal that addresses the following:
1. A description of the problem domain (written using terminology that a user of the system would use; no need for technical database terminology.)
2. Choose a domain with which you are familiar. Your domain should be different than examples provided in the textbook and in class.
3. Identify a set of requirements for a system that is appropriate for your domain. If you wish, you may choose an appropriate sub-set of a larger domain.
4. Your work should be original. Do not copy or download from the Internet. If you do search the Internet to get ideas, you must cite your sources in an APA-style reference page.
Intersession 2: Conceptual Modeling Design
You need to develop an Entity Relationship Diagram (ERD) for the database that models your chosen domain. This assignment consists of three steps that should be completed.
1. Identify entities;
2. find relationships; and
3. Draw the ERD blueprint.
Step 1: Identify Entities
Identify the entities. These are typically the nouns and noun-phrases in the descriptive data produced in your analysis. Do not include entities that are irrelevant to your domain.
For example, in a college database project, the entity candidates are departments, chair, professor, course, and course section. Since there is only one instance of the university, we exclude it from our consideration for now.
Step 2: Find Relationships
Discover the semantic relationships between entities. In English, the verbs connect the nouns. Not all relationships are this blatant; you may have to discover some on your own. The easiest way to see all the possible relationships is to build a table with the entities across the columns, and down the rows. Then, fill in cells to indicate where relationships exist between the entities.
Departments Chair Professor
Departments
Chair
Step 3: Draw the ERD Blueprint
Draw the entities and relationships you have discovered. View the example of a college database project ERD blueprint
. To create your ERD, the domain (or subset of a domain) that you chose for your project should include the following characteristics:
• Size. An appropriately sized domain results in a database with about a dozen entries (more or less).
• Relationship. The entities comprising your domain should be interrelated.
• Functionality. The scope of the diagram shows the operations or functions that the database project addresses. It also identifies the functions that fall outside of the application.
• Description. Define the data requirement of your entities. For example:
o Student Entity: Members of the public who register and pay for courses are considered students. The data stored on each student includes student number, name, address, email address, previous classes, and experience. Also stored is the date for registration and the classes they are registered in. The student number is unique for each student.
o Course Entity: The school offers a variety of Online design courses through its website (these are considered course, not the on-location seminars). The data stored on each course includes the course number, the name of the course, the course description, and prerequisites (if any). The course number is unique, etc.
Intersession 3: Logical Modeling Design & Relational Schema
You need to work on the Logical Modeling Design and to specify the entries, columns and their relationship. Use an entity-relationship ER diagram to visualize the database.
Your schema should include some attributes that make it possible to include some transactions that involve aggregate functions. For example, a school schema would allow for queries to calculate enrollment in each section of the average enrollment in courses for a given department, or the total courses being taught by each instructor, etc. This should also make interesting constraints and triggers possible. Review this basic example of a requirements document and Conceptual Data Model.
Turn your ER diagram into a normalized relational database design for the (subset of the) domain (i.e. a set of tables, each with appropriate attributes, a primary key, and appropriate foreign keys. The database should be based on the ER diagram, but one-to-one and one-to-many relationships may be implemented by appropriate attirbutes in the "one" entity, rather than as separate tables. You relational database must be at least in 3NF. Remember to determine the cardinality of the relationships. You may want to decide on cardinality when you are creating an Entity-relationship diagram ERD relationship table
Intersession 4: Physical Modeling Design & Structured Query Language (SQL)
We focus on physical modeling design with the "representational aspects" and the "operational aspects" of the database. In the following weeks, we work in the internal DBMS specific operations and processes and how the DBMS interacts with the data, the database and the user. The translation from logical design to physical design assigns "functions" to both the machine (the DBMS) and the user. Functions such as storage and security, and additional aspects such as consistency of data and learnability are dealt with in the physical model/schema. Practically speaking, a physical schema is the SQL code used to build the database.
You can use any of the Online SQL systems discussed in class, Microsoft Access, or download and install a SQL Script Editor for your computer. A copy of the SQL code is required for this assignment. You will build the front-end for the user (DBMS) at the end of the intersession weeks.
Use SQL statements to populate your Database Relational Schema data. Implement your design by generating a file of SQL to create the code script. This should include:the creation of tables, but also (as appropriate) the creation of named, data type, and constraints. Constraints are used to limit the type of data that can go in a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.
Intersession 5: Transaction Requirements. SQL Queries
For this assignment, you need to submit your code (SQL script or document), including structure from the Data Definition Language (DDL) and the Data Manipulation Language (DML). Use these SQL Statements as a starting point for your script.
The data manipulation language (DML) is used to create, read, update, and delete data from a table. The data definition language (DDL) is used to create and alter the structure of the database itself, like creating a table, it's columns, define and modify the primary and foreign keys, stored procedures, delete columns, tables, and even the entire database. Your SQL statements should require a variety of SQL capabilities, such as various kinds of join, aggregate functions, etc. (This presupposes a good initial domain choice.)
Remember you must turn in code (or documentation) of the results of these tests indicating that the SQL statements for your requirements worked correctly and that your constraints correctly allowed good data and caught bad data. Your document must include comments (handwritten or entered as SQL comments in a text file) that indicate what each test is testing - i.e. a specific requirement (if "good" data) or a specific problem you were catching (if testing a constraint on "bad" data).
Follow these steps to complete your assignment:
1. Populate your database with sample data to allow testing of the schema and the various transactions. Each table should have a minimum of ten rows.
2. After you've entered data, create some sample queries to see if the output is what you expected.
3. Create some sample reports to make sure these also produce similar results.
4. Make any necessary corrections to the database design during this stage and before you enter all of your personal records. If you find any problems, correct the design.
5. Enter all of your personal records.
6. Write your SQL Script.
Submit your code (SQL script or document), including structure from the Data Definition Language (DDL) and the Data Manipulation Language (DML).
Intersession 6: Database Management Systems (DBMS)
Up to this point in the course, we have been building our Database using concepts of design and Structured Query Language (SQL). Now, your database should be ready to be deployed into action. This usually means the database structure and relationships have been thoroughly tested and everything functions as anticipated to begin the development of a Database Management Systems (DBMS) or Web pages (GUI) that will interact with it.
In the next couple weeks, you need to work with a database management system (DBMS) that will be your Graphical User Interface (GUI) for building and maintaining your database. Use this additional information on DBMS functions and resources as you complete your assignment.
Find and submit a database management system (DBMS) proposal for your final project. Choose a DBMS that you believe you will be able to execute during these last couple weeks. Your proposal must include the name and description of the DBMS (documentation and/or links). Include 1-2 paragraphs explaining why this DBMS interests you. Also, share a few challenges you anticipate by using the DBMS in your database project.
Be sure to cite any sources you used to develop your DBMS project proposal. It could be helpful to look ahead at the Intersession 7: Database Management Systems (DBMS) Implementation assignment to find out how you will be using the DBMS.
Format your write-up in APA format with a title page and reference page (if applicable). Submit your write-up document and any additional files for your proposal.
Intersession 7: Final Project Database Management System (DBMS) Implementation
The implementation phase is where you install the Database Management System (DBMS) on the required hardware, optimize the database to run best on that hardware and software platform, and create the database and load the data. The initial data could be either new data or existing data imported from your SQL scripts. You also establish database objects this week and give the users that you've identified access applicable to the database requirements.
Keep in mind that you are going to present your DBMS at the final project presentation day.
The following are steps for the implementation assignment:
1. Install the DBMS.
2. Tune the setup variables according to the hardware, software and usage conditions.
3. Create the database and the tables. (Every table must have a primary key, which uniquely identifies rows in the table and validation rule).
4. Establish relationships between tables.
5. Load the data into the tables.
6. Create at least three forms (splash screen and Main switchboard are optional).
7. Create at least four transaction requirement Queries. (from week 5).
8. Create data views and reports.
If it is necessary for your DBMS, all transactions can be handled by SQL scripts. Submit a document report essay with screenshots and any additional files. Format your write-up in an APA-style essay an include APA-style references (if applicable). You do not need to include a title page for this assignment. Grammar, spelling, and quality will influence your grade.
Project Presentation
You need to submit your final project presentation. Include in your presentation a summary of every final project intersession:
1. Conceptual Modeling Design
2. Entity Relationship Diagram (ERD)
3. Logical Modeling Design
4. Relational Schema
5. Physical Modeling Design
6. Structured Query Language (SQL)
7. Database Management Systems (DBMS)
8. Database Management System (DBMS) Implementation
Your presentation should consist of at least 12 slides, not including the title or reference slides, and be between 5-10 minutes in length. Your presentation should be engaging and provide a thorough review and summary of all components of your written final project study case. Slides should be concise and uncluttered. Use various graphics and visual enhancements when appropriate. Be sure to provide APA-style citations for any references and images used.