Your final project is to develop a database from the ground up, including the conceptual modeling design, entity relationship diagram (ERD), logical modeling design, relational schema, physical modeling design, Structured Query Language (SQL), Database Examples for your final project:
Choose one of these examples:
Human Resources (HR) Employee Management System: This system allows HR staff to manage information about each hired employee.
Information Services (IT) Asset Management System: This element allows the IT staff to maintain information about the computer hardware, software, and peripherals owned by the organization.
Departmental Intranet Management System: This system allows a liaison from each department to update the individual departmental information page. This reduces the need for IT staff to make updates to the intranet every time a department makes a change.
College Database Project: A college contains many departments that each offer any number of courses. Each department employs many instructors, but an instructor can only work in one department.
Hospital Management System: A full description of the personal patient detail, phone number, and updated health history records. The doctor handles patients, and one doctor can treat more than one patient. Each doctor has a unique ID. Doctor and patients are related. Patients can be admitted to the hospital and are assigned a hospital room. There are patient rooms, operation theaters, and ICU rooms in the hospital.
Hotel Management System: A hotel is a hive of numerous operations including, front office, booking and reservation, banquet, finance, HR, inventory, material management, quality management, security, energy management, housekeeping, customer relationship management (CRM), and more. The hotel has different categories of rooms that each have different prices. Some hotel employees manage the services offered to customers. The customer can book a room either online or by cash payment at the hotel.
Step 1: 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.
A description of the problem domain (written using terminology that a user of the system would use; no need for technical database terminology.)
Choose a domain with which you are familiar. Your domain should be different than examples provided in the textbook and in Blackboard.
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.
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.
develop an Entity Relationship Diagram (ERD) for the database that models your chosen domain. This assignment consists of three steps:
find relationships; and
Draw the 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:
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.
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.
Step 3: 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. (https://alt-5c06bfb1d2454.blackboard.com/bbcswebdav/courses/IT-8100-MASTER-EMBA-2020/Intersession%20Assignments/Week_3-Example%20of%20Requirements%20Document.html?one_hash=84F0543C6EC22B5CD951EF0DF5886982&f_hash=ED75839D6D834FF6804D940BA38DEAD0)
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 attributes in the “one” entity, rather than as separate tables. Your 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.
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.
Step 5: 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.
This week, 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 of 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.
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.
The following are steps for the implementation assignment:
Install the DBMS.
Tune the setup variables according to the hardware, software, and usage conditions.
Create the database and the tables. (Every table must have a primary key, which uniquely identifies rows in the table and validation rule).
Establish relationships between tables.
Load the data into the tables.
Create at least three forms (splash screen and Main switchboard are optional).
Create at least four transaction requirement Queries. (from week 5).
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.