Scribd
Upload
24 views6 pages

Attendance & Grade Database Design Guide

This assignment requires students to design a conceptual database for an Attendance Taking & Student Grade Management System at FPT University, taking approximately 25 hours to complete. Students must submit their work as a single PDF document or hardcopy before the semester's last week, ensuring proper referencing to avoid plagiarism. The final report must include an ERD, relational schema, SQL statements, and various queries demonstrating the database's functionality.

Uploaded by

Vu Tuan Phuc (K16HL)
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
24 views6 pages

Attendance & Grade Database Design Guide

This assignment requires students to design a conceptual database for an Attendance Taking & Student Grade Management System at FPT University, taking approximately 25 hours to complete. Students must submit their work as a single PDF document or hardcopy before the semester's last week, ensuring proper referencing to avoid plagiarism. The final report must include an ERD, relational schema, SQL statements, and various queries demonstrating the database's functionality.

Uploaded by

Vu Tuan Phuc (K16HL)
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

This assignment should take an average student who is up-to-date with tutorial work

approximately 25 hours

Deadline: your work must be submitted before the last week of this semester.

Plagiarism is presenting somebody else’s work as your own. It includes: copying


information directly from the Web or books without referencing the material;
submitting joint assignment as an individual effort; copying another student’s
assignment; stealing or buying assignment from someone else and submitting it as
your own work. Suspected plagiarism will be investigated and if found to have
occurred will be dealt with according to the procedures set down by the University.

All material copied or amended from any source (e.g. internet, books) must be
referenced correctly according to the reference style you are using.

Assignment Submission Requirements

 For this assignment you must submit a single Acrobat PDF document / Hardcopy (depending on
your lecturer decision)

1>Detailed Specification
This is an individual assignment.
You are asked to provide a conceptual database design for Attendance Taking & Student Grade
Management System at FPT University (I know, FPT already got one, but let assume we are
reconstructing it).
You can base on existing sources to develop your design including: [Link], [Link], ...
Attendance Taking Sub-System
Every semester, Lecturers and students will receive their schedules for classes. The below figure displays
an example of the particular lecture, you can check your own to see the detail schedule for student.

1
Each class, the lecture going to take attendance for student, all of their absent/present will be recorded.

The list of students is grouped by the training department (no need to worry how they can create the list;
it is a different story to concern).

2
Hint: check the instructions you got before

3
Student Grading Management Sub-System
For each subject that attended by the student, the lecture will give score to the assessment to each of their
assessment. Below figure shows an Example of the assessments for course DBI202.

Students can check their results at the end of semester as following example:

Each Subject code, student can check their detailed result of as below example:

4
2> The database must consist of at least six tables that have been populated with data. The database is
to support queries that would typically be submitted to the system for the topical area that you have
chosen. You must do the following:

Self-investigation for the requirement of the system. Listed them all as form of reports, business rules.

· Using UML, Chen’s notation to create an Entity Relationship (ER) model for your relational
database. All entity types, their attributes and relationships must be clearly shown. You will also
be required to show all cardinality and participation constraints. You should use some enhanced
ER features in your conceptual model where it makes sense to do so.
· Map the EER model devised in part (1) into a relational data model. It must be normalised up to
at least 3rd Normal Form.
· Using appropriate SQL commands create a set of database tables in MS SQL Server 2008+. You
should also show all constraints used in the creation of the tables.
· Populate the database with a small amount of data. The data should be meaningful but does not
need to be extensive. The following sites may be useful for quickly generating data:

 [Link]
 [Link]
 Your database must contain one view, one trigger, on store procedure and an index (describe
why).
 Create 10 sample queries that demonstrate the expressiveness of your database system. Your
queries must demonstrate different aspects of the system.

Final Report

You must submit a brief final report which must include the following:

a) A brief description of the database including any assumptions made during the design
(THIS IS VERY IMPORTANT TO CLERIFY THE ASSUMTIONS in form of business
rules).

5
b) An ERD (Entity Relationship Diagram) that fully describes the database (giving
descriptions on your work would be appreciated).

c) The relational schema derived from the ERD that is at least in 3NF (Any detail of the
process would be appreciated).

d) The set of database statements used to create the tables used in your database. You do
NOT need to include all the data and insert statements.

e) 10 queries that demonstrate the usefulness of the database. Also state why and when
each query would be used. The following must be demonstrated by at least one of your
queries:

o A query that uses ORDER BY

o A query that uses INNER JOINS

o A query that uses aggregate functions

o A query that uses the GROUP BY and HAVING clauses

o A query that uses a sub-query as a relation

o A query that uses a sub-query in the WHERE clause

o A query that uses partial matching in the WHERE clause

o A query that uses a self-JOIN

f) The trigger, store procedure, and the index should be added (explain why you make it)

Demonstration

You will be required to briefly demonstrate your system in one of the laboratory sessions prior to
submission of the report.

Common questions

Powered by AI

The critical components of the assignment submission include: creating a conceptual database design, using existing sources for design development, and ensuring the database supports specific queries. These components contribute to the learning goals by reinforcing the student's ability to design and implement a database system from conceptual to practical stages, including using UML for ER modeling and normalizing the database to 3NF to ensure structure and integrity . Additional requirements like creating SQL tables, populating them with data, and demonstrating queries enhance the student's technical SQL skills and understanding of database functionalities .

Including views, triggers, stored procedures, and indexes in the assignment highlights their role in optimizing performance and automating database tasks. Views abstract complex queries, providing simplified data access to users, while triggers enforce business rules automatically when database events occur. Stored procedures encapsulate SQL logic for reuse, ensuring consistency and security. Indexes speed up data retrieval operations, enhancing overall database performance. Together, these elements demonstrate how to increase efficiency and maintain scalability in a real-world database system .

Implementing a database using MS SQL Server 2008+ prepares students by familiarizing them with tools commonly used in the industry, thereby bridging the gap between academic learning and real-world application. This experience with a robust database management system equips students with the technical skills necessary for entering the workforce. Additionally, it fosters an understanding of database server management, including aspects of security, transaction handling, and performance tuning, which are critical in professional environments .

Self-investigation for determining system requirements encourages a deep understanding of the operational needs and the environment in which the database will function. This process enables students to make informed decisions about the database structure, ensuring that it effectively supports required queries and operations. By forming business rules and requirements through self-investigation, students gain insights into problem areas and user expectations for the system, ultimately leading to a more robust and relevant database design .

Students may encounter challenges such as correctly identifying entities and relationships, maintaining referential integrity, and ensuring that the model adheres to normalization standards. To address these, students should focus on thoroughly understanding the differences between logical and physical database models, practicing meticulous conversion of entity types and attributes into tables with appropriate key constraints, and repeatedly consulting normalization rules to ensure optimal structure. Incremental testing and seeking feedback can also aid in overcoming challenges in translating an EER model into a relational data model .

Incorporating plagiarism guidelines and submission requirements ensures academic integrity, promoting original work and ethical standards in the educational process . By preventing dishonest practices, students are encouraged to engage in critical thinking and genuine understanding. The framework directs students to reference correctly, preventing unintentional plagiarism and emphasizing the importance of academic honesty, which is crucial for maintaining the credibility of educational outcomes .

Sample queries that demonstrate different SQL features enhance student understanding by providing practical experience with SQL's versatility. Queries using ORDER BY, INNER JOINS, aggregate functions, sub-queries, and other features illustrate how complex data retrievals can be executed effectively. This hands-on approach allows students to apply theoretical knowledge of SQL in practical scenarios, improving their problem-solving skills and proficiency in crafting efficient queries that leverage the full capabilities of SQL .

Normalizing the relational data model to the 3rd Normal Form (3NF) eliminates data redundancy and ensures logical data structure, which is crucial for maintaining data integrity and optimizing database queries. In the context of the assignment, achieving 3NF helps prevent update anomalies and ensures that dependencies are properly maintained, thus enhancing the reliability and efficiency of the database system .

Requiring a demonstrative session of the database system fosters improved communication and presentation skills. Students must concise articulate their design rationale, defend their choices, and effectively demonstrate system functionality. This practice prepares them for real-world scenarios where they must convey technical information to both technical and non-technical stakeholders, enhancing their ability to effectively showcase their work, anticipate questions, and respond confidently .

Using tools like UML and Chen's notation for creating an ER model provides standardization and clarity in representing entities, attributes, and relationships in database design. UML offers a universal language that facilitates communication among team members and educators, helping to clearly define structure and behavior. Chen’s notation, with its intuitive diagramming of entities and relationships, helps in visualizing the database structure, making it easier to identify and address design flaws early in the process .

You might also like