tobic last page >SQL
DATABASE DESIGN TERM PROJECTS Student ID Student Name Note: This is your hands on training opportunity that covers your learning during the class lectures i.e., starting from requirements analysis towards implementation of an enterprise database. This project must consist of your own work, completed during the semester. Please go through all these steps and prepare your document according to the following format. Part 1: Requirements Analysis Overview of Company/Product(s) Provide a one-page document entitles "Business Overview" i.e., the business for which you want to create a "database management system", which identifies: a. Your business (e.g., Student management system) b. Type of business (e.g., educational/wholesaler/internet shopping etc.) c. Short description of the company/ business d. Products Part 2: Conceptual Design 1. Create an ER diagram for various entities along with their relationships based on the information you collected during requirement analysis. Only include the entities and relationships identified: a. Entities b. Named Relationships Example: Entity: Student, Instructor Relationship: A teacher teaches students. Part 3: Detailed Design 1. Modify the ER diagrams from Conceptual design (i.e. Part 2) for various entities along with their relationships, include attributes, primary keys, foreign keys, optionality and cardinality; a. Entities b. Relationships c. Attributes d. Optionality and Cardinality e. Unique Identifiers (i.e. Primary Keys) 2. Create a second ER diagram that resolves many-to-many relationships (if there is any). 3. Carry out Normalization, if required. Fill the following table for above ER diagram. Indicate "Foreign key". Part 4: Physical Model (Mapping) 1. Convert the logical model into a physical model using a tabular format (not implementation) Eatily Type: STUDENT Atributes Data Type Required Optional Stadcat ID lateger (10) Repuired Name Varctartso) Required Teacherein lexeger(10) Required Address: Varchar(30) Regaired Enity TYpe: TEACHER Attribute Dats Type Requirod Optional Name Vartart(50) Repuired Desigemion Varhur(25) Reyuired Dept Varchar(30)Required Part 5: Implementation of Physical Model Note: Implement into any DBMS (try to install MySQL on your machines) and share screenshots - Construct CREATE statement for each table. Consult above table in Part 4. Example: Create statement for TEACHER table: create table TEACHER ( TEACHER-ID int (10) not null, NAME varchar(50) not null. Note: Implement into any DBMS (try to install MySQL on your machines) and share sereenshots - Construct CREATE statement for each table. Consult above table in Part 4. Example: Create statement for TEACHER table: create table TEACHER ( TEACHER-ID int(10) not null, NAME varchar(50) not null, DESIGNATION varchar(25) not null, DEPT varchar(30) not null, primary key (TEACHER-ID) ) Example: Create statement for STUDENT table: create table STUDENT ( STUDENT-ID int(10) not null, NAME varchar(50) not null, TEACHER-ID int(10) references TEACHER(TeacherID), ADDRESS varchar(30) not null, primary key (STUDENT-ID) ): Part 6: Design queries Populate the tables with dummy values. Design as many queries as possible? Run queries against the database and share the resultant screenshots. Example: - How many tables (i.e. entities) are there in the database? - List the teacher of every student? - Number of students taught by each teacher? - Etc. Submission Deadline: Grade Points: End of Week 10. Guidelines: - Identify at least 15 entities in your project. - The project should be done in groups (maximum 3 students per group). a. Each group must select a unique project topic. i. Consult course instructor with your choice and assignment of selected topic - Project must provide accurate and related documentation. The report MUST be written in the same word document Note: Implement into any DBMS (try to install MySQL on your machines) and share sereenshots - Construct CREATE statement for each table. Consult above table in Part 4. Example: Create statement for TEACHER table: create table TEACHER ( TEACHER-ID int(10) not null, NAME varchar(50) not null, DESIGNATION varchar(25) not null, DEPT varchar(30) not null, primary key (TEACHER-ID) ) Example: Create statement for STUDENT table: create table STUDENT ( STUDENT-ID int(10) not null, NAME varchar(50) not null, TEACHER-ID int(10) references TEACHER(Teacher- ID), ADDRESS varchar(30) not null, primary key (STUDENT-ID) ): Part 6: Design queries Populate the tables with dummy values. Design as many queries as possible? Run queries against the database and share the resultant screenshots. Example: - How many tables (i.e. entities) are there in the database? - List the teacher of every student? - Number of students taught by each teacher? - Etc. Submission Deadline: Grade Points: End of Week 10. Guidelines: - Identify at least 15 entities in your project. - The project should be done in groups (maximum 3 students per group). a. Each group must select a unique project topic. i. Consult course instructor with your choice and assignment of selected topic - Project must provide accurate and related documentation. The report MUST be written in the same word document Few Sample Topics Library Management System Database > Hospital Management System Database > Student Record Keeping Database > Hotel Management System Database Insurance Management System Database Electric Bill System Database Course management system database Employee attendance system database > Online Shop Database undamentals of Database Systems-2511 - Term Project
