Database Project Assignment 1
I. Instructions:
A. In this file, you will find a list of database project ideas. You must choose one of them to design and implement.
B. You can work on this project as a group (maximum 3 students) or individually. You have to send the names of group members to your instructor by the end of week 7.
C. This project will be distributed as in the following:
a. Design the database, following an ER model.
b. Normalize the tables ( each table should be in 3NF at least)
c. Use MySQL or any other database to create the normalized tables and populate your tables with at least 5 rows.
d. Execute the sample requested queries.
4. Each student has to submit one report about his/her chosen Project via the Blackboard (Email submission will not be accepted which will be awarded ZERO marks) containing the following:
a. ER Diagram.
b. All schemas before and after normalization.
c. All SQL statements of:
- creating tables
- inserting data in tables
- queries.
5. Screenshots from MySQL (or any other software you use) of all the tables after population and queries results.
6. You are advised to make your work clear and well-presented, marks may be reduced for poor presentation. This includes filling your information on the cover page.
II. Project Ideas:
Project 1
Online Library Management system
This is supposed to develop a database to manage online library management system (A student and faculty can issue books). Your product should contain information about courses, students, teachers, Departments Book Id, book names, authors, No. of copies, number of days and Details of Fine.
Each Book has id, title, author, number, submission date and time, duration, issue date You should store an id, name, E-mail for each student & teachers.
Each teacher has id, name, department name.
For new students enrolled to university and you have to store information like Name and ID.
Database should also store who issues that book and when and duration. Details of Fine (when the book is not returned at a due date) is also stored.
SQL Queries:
1. Write a query to find which student has issued the Book Id =1011 in the last 3 months.
2. Find the students who submitted a book after the deadline in "computer science" course.
3. List the name and email for all students who supposed to return the book, but they did not.
4. Show the total book of each course that a student has taken.
5. Show the students who has issued that book and when and duration. Detail of Fine (when the book is not returned at a time).
Project 2
Online Hospital Management System (OHMS)
The project "Online Hospital Management System" is aimed to develop to maintain the day-to- day state of admission/discharge of patients, list of doctors, reports generation etc. It is designed to achieve the following objectives:
1. To computerize all details like patient details & hospital details.
2. Scheduling the appointment of patient with doctors to make it convenient for both.
3. Scheduling the services of specialized doctors and emergency properly so that facilities provided by hospital are fully utilized in effective and efficient manner.
4. If the medical store issues medicines to patients, it should reduce the stock status of the medical store and vice-versa.
5. It should be able to handle the test reports of patients conducted in the pathology lab of the hospital.
6. The inventory should be updated automatically whenever a transaction is made.
7. The information of the patients should be kept up to date and their record should be kept in the system for historical purposes.
SQL Queries:
1. Find all the patients who are admitted in the hospital on a particular date.
2. Find the patient who did not pay for the test report.
3. Find the list of doctors who has specialization in Surgery.
4. Show the total number of doctors in each department of the hospital.
5. Show the total amount each department of hospital received on a particular date.
Project 3
Database System for a Blood Bank Organization
Consider a database system for a Blood Bank Organization. The data requirements are summarized as follows:
1. Donors:
1. Donors are tracked by a unique donor ID.
2. Personal information includes first name, last name, phone number, Date of Birth and sex.
3. The combination of the Donor's first and last name must be unique.
4. Donor's sex is not a mandatory field.
5. Donors must be at least 17 years of age.
6. Donors can donate at any clinic any number of times.
2. Blood:
1. Blood is tracked by a unique ID.
2. BloodType is how the blood is categorized: (A+/A-, B+/B-, AB+/AB-, O+/O-, N/A).
3. N/A refers to blood that has not been processed yet.
4. Status refers to the state of the blood: (pure, N/A, impure).
5. N/A refers to blood that has not been processed yet.
6. Donor_id is a FK which references the ID of the Donor.
7. Blood specimens must have a donor.
8. Clinic_id is a FK which references the ID of the Clinic that stores the blood.
9. Blood specimens must be stored at one clinic.
10. DonateDate is the date the blood sample was received.
3. Clinic:
1. Clinics are tracked by unique clinic ID.
2. Each clinic has a name and location.
3. The combination of the clinic's name and location must be unique.
4. Employees:
1. Employees are tracked by unique Employee ID.
2. Clinic_id is a FK which references the Clinic ID which they work at.
3. Employees must work at a single Clinic.
4. Personal information includes Fname, Lname and sex.
5. The combination of an employee's first and last name must be unique.
SQL Queries:
1. List the first and last name of all donors whose blood type is O+.
2. List names and location of clinics that store a blood of type B+.
3. Find the names of donors whose age is above 30 years and have donated since 1/1/2000.
4. List the names of employees who are working in clinics which do not have a blood of type A-.
III. Your Project:
Title of Your Project
A. Design & ER Model
B. Normalize the Tables (in 3NF at least)
C. Create the Normalized Tables and Populate them with at least 5 Rows
D. Write the sample requested Queries & Execute them.
Project Assignemnt 2: Human Computer Interaction
Introduction
Purpose
Your goal is to create a design for a software interface. You will experience the scope of the design process from brainstorming ideas and gathering information about users' needs to storyboarding, prototyping, and finally, testing and refining your product.
As you work on the software interface, you will demonstrate your ability to apply fundamental Human-Computer Interaction principles to interface analysis, design, and implementation. You will be responsible for delivering project components to your professor at several points during the course.
Deliverables
This project should follow the main steps of the first three phases and presentation. Details description and diagrams should be included in each phase.
Phase One:
You will need to decide on an idea for an interface. It could be a web site, a mobile app, an appliance touch screen, etc. Don't make your idea too broad. Focus on something that solves a problem or fills a need.
Another approach is to take an interface that is very poor and reimagine it. Your project idea needs to be sufficiently complex to show your ability to design multiple screens and interaction flows as the user carries out a small number of tasks. For that you need to do the following:
1. Brainstorm ideas. You might want to use a free, online mind-mapping tool like SpiderScribe or Mindomo (Optional). Here are some questions you might want to consider to focus your brainstorming:
a. Think of a service you or your friends use. What's the biggest avoidable hassle (inconvenience) customers must endure?
b. What happens in your daily routine that is complex or confusing? How could you simplify it?
c. Think of a domain that interests you: shopping, elementary education, exercise, social media, etc. What possibilities are there to do something new or better? What might be redesigned for newer or different technologies like smart phones or gaming platforms?
2. Narrow your choices down to one idea.
3. Write a brief description of the problem, including the following main points:
a. The Problem
b. Why It's Interesting
c. Main Users Affected
d. Current Solutions (if any)
e. Make a list of the needs/goals/tasks you observed (at least 10). Group and organize this list as appropriate.
f. A brief literature review of at least two literature resources related to your idea. The resources can include peer reviewed journal and conference papers, books, product manuals and product websites. All resources must be cited in APA style.
Phase Two:
A high-fi prototype shows the main elements of a user interface connected and working together using a software tool. Its purpose is to get a quick clear and precise look of the final interface, which provides both designer and user the ability to interact with the system and check some of its functions. You can use a high-fi prototype to identify usability issues such as confusing paths, bad terminology, layout problems, and missing feedback. Please note that your prototype does not need to be as extensive, 3-5 interfaces to show the main functions will be sufficient. Your prototype should allow people to navigate from screen to screen, recover from errors, and change their choices. Don't try to show every possible action or detail. Focus on the main interactions.
1- Using any software prototyping application, create a prototype for your interfaces following the instructions above.
2- Write a description of each of the interfaces (include screenshots) explaining the following:
a. The purpose of the interface.
b. The main functions in the interface.
c. The previous and next status of each action.
d. The layout of the interface.
Phase Three:
1- You will conduct a heuristic evaluation of your prototype with expert users. Your classmates in this course would make great expert reviewers. You should use Jakob Nielsen's Ten Usability Heuristics or another set of heuristics approved by your professor.
a. Allow at least 3 expert users to use the high-fi prototype.
b. The purpose of the heuristics is to guide your expert users and help them find as many different types of usability issues as possible.
c. Instruct your experts to describe each issue noted as specifically as possible. Allow the expert to explore the interface and follow any paths.
2- Make sure to practice with your prototype so that you can operate it effectively and not waste time. Doing several practice runs or walkthroughs will help you identify missing pieces and dead ends.
3- It would be a good idea to do this testing in groups. One person can be the expert tester while another works the prototype and the others take notes. Then, everyone can switch roles until all participants have been experts and had a chance to test their designs.
4- Review the feedback you got from your reviewers.
5- Write a summary of the prototype testing results describing what worked and what did not. In addition to a list of specific changes you want to make to your interface. This summary must include;
a. Average subjective satisfaction of reviewers
b. Number and details of terms/metaphors unfamiliar to reviewers
c. Number and details of action sequences considered confusing lengthy by reviewers
d. Number and details of inputs/outputs not understood by reviewers
And you can add any further items of interest from review activity.
Phase Four:
Prepare a presentation of your project idea describing all of the following:
1- Background: Introduce the idea of project you chose including problems with existing interface and its possible impact.
2- Summary of Work: Explaining what your implemented changes to interface accomplish, how you designed it, and what possible improvements were brought in comparison to problems you listed earlier.
3- Features: Describe of the main interface elements and features you implemented
4- Reviewer Testing: Describe briefly the reviewer evaluation results as you compiled those in your report including all problems identified.
5- Evolution: Describe how your implemented interface makes the system better from perspective of usability and user satisfaction. List all improvements experienced by reviewers or other sources who evaluated your interface. Include graphics to show before and after.
6- Conclusion: Briefly discuss the conclusion and any future improvements you can suggest about interface of your selected system. Address problems that remain and what your next steps for the product might be if you were to continue further working on same system.
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also include a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also Include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.
Attachment:- PROJECT-Details.rar