Database Systems Project 01

--- title: Database Systems Project 01 --- <h1 style='border: none'><center>Database Systems Project 01</center></h1> <h1 style='border: none'><center>Students Attendance</center></h1> <h5><center>The Islamic University of Gaza Engineering Faculty Department of Computer Engineering</center></h5> <h6>Authors: Usama R. Al Zayan & Rasha E. Kahil2023/04/05</h6> --- <h2 style='border: none'><center>Deadline: 11:59 PM, 26th May, 2023</center></h2> <h4 style='border: none'><center>Female discussion Date: 27th May, 2023</center></h4> <h4 style='border: none'><center>Male discussion Date: 28th May, 2023</center></h4> # Goal The goal of this project is to provide a realistic experience in the conceptual design, logical design, implementation, and operation of a relational database applications. # Introduction In this document, a description of the application, its general requirements, and requested features shall be provided, then specify how you should provide your deliverables. In this project, it is intended to provide you with an experience that is as close to a real project as a third-year student. Moreover, this document will be describing the requirements in Arabic, hoping to copy the experience of you speaking to a client in a real project. The project can go well beyond the minimal requirements outlined in this document. Any student who wishes to add his extensions to the project can do that freely. In fact, it is highly encouraged to have such extensions and will for sure reward outstanding projects generously. The description given here of the project you are implementing is necessarily sometimes vague or incomplete. This is by design; in real life, your “customers” are managers or businessmen, who usually lack a comperhensive knowledge in software to give you the perfect description. You will need to fill the gaps in the description by asking your lecturer or tutors the right questions in order to create a precise design, and concrete implementation as well. You are motivated to discuss the requirements on our group. Eng. Usama and Eng. Rasha will jump in the discussion when necessary. Of course, we will be happy, if one can clarify the vague points to each other or agree on the right questions to ask. Read this document carefully: It will give you all information you need to know; regarding the organization of the project. It shall also have the answers to most of your questions, so be sure you know all what is mentioned here before asking around. # Requirement description As promised, I will provide the description in Arabic to mimic a real client. المطلوب منك مهندسنا العزيز هو عمل برنامج لمتابعة حضور الطلاب في المحاضرات التي يقوم المدرسون بتدريسها للطلاب. It is required from you our dear engineer to make a program that tracks the attendence of the students in the lectures given by the instructors. تهتم الجامعة بحضور الطلاب في كافة المحضرات، لذى سيتم سحب الطلاب غير الملتزمين بالحضور ما يزيد عن 25% من عدد محاضرات المساق في الفصل. وقد كان المعيدون مشكورين يقومون بمتابعة الحضور والغياب باستخدام ملفات اكسيل ولكن مع زيادة أعداد الحضور وكمية المحاضرات، وتفرق الملفات وبطء الإدخال أصبحت متابعة الحضور صعبة جدا، حتى ان بعض المتابعات تنتهي بالفشل للأسف. ونود منك أن توفر لنا حل سهل الاستخدام يمّكّننا من المتابعة ويغنينا عن الاكسيل. The university system cares about the attendence of the students in all lectures. Therefore, any student will be deregistered from any course, if this student did not attend more than 25% of the lectures in the semester. Thanks to our tutors who are tracking the attendence and persence of students by using excel based files. However, due to high demand and large number of students and lectures, it was mandatory to have better system to track the attendence. Thus, this project shall provide a solution which is user-friendly and easy to use rather using excel files. الحل المقدم يجب أن يحتوي الميزات التالية: <ul style='text-align:justify;direction:rtl;font-family:Traditional Arabic;font-size:24px'> <li>إمكانية إنشاء وتحرير مساق واضافة تفاصيله كالموضوع والكتاب والمدرس لهذا المساق، ومكان الانعقاد الافتراضي(القاعة).</li> <li>إمكانية انشاء حسابات مستخدمين ليتكمن المعيدون من استخدام النظام. </li> <li>إمكانية إضافة وتحرير محاضرات ضمن مساق معين واضافة تفاصيل المحاضرة كالعنوان والمكان الذي بالعادة يكون قاعة المحاضرة الافتراضية الا في حالات نادرة. </li> <li>إمكانية تسجيل وتحرير طلاب من كل الجنسين، وتسجيل انتماء طالب معين إلى مساق معين، وتسجيل وتحرير حضورهم للمحاضرات. </li> <ul> <li>يجب تسجيل الاسم رباعي، الرقم الجامعي، ورقم الجوال (أو الجوالات)، ومنطقة السكن لكل طالب. </li> <li>يحب تسجيل معلومة حضور الطالب الفلني للمحاضرة الفلنية أو تغيبه عنها. </li> <li>يجب أن تتم عملية تسجيل الحضور باستخدام الرقم الجامعي أو رقم الجوال أو الاسم ويجب أن تتم بسرعة لكيلا نؤخّر المدرس عن بداية المحاضرة لذلك نود أن يدعم البرنامج خاصية الإكمال التلقائي عند الإدخال. </li> <li>في حالة تغيب المعيد المسؤول عن البرنامج سيقوم معيد آخر مشكورا بإدخال الأرقام الجامعية لجميع الحضور في ملف اكسيل، ونود من البرنامج أن يوفر إمكانية استيراد هذا الملف كحضور لمحاضرة معينة. </li> </ul> <li>ملاحظة: كلمة تحرير أعله دائما تشمل التعديل والحذف. </li> <li>إمكانية عرض كشوف الحضور والغياب لكل محاضرة من المحاضرات مع نسبة الحضور وعدد الحضور في كل محاضرة. </li> <li>إمكانية عرض جميع المحاضرات المنتمية الى مساق معين. </li> <li>إمكانية عرض تقرير الحضور لكل طالب في مساق معين بسرعة عند طلب الطالب ذلك أثناء تسجيل حضوره في أحد المحاضرات. مع توفير وسيلة لتعديل الخطأ في التقرير بسرعة. </li> <li>إمكانية استخراج كشف بجميع الطلبة غير الملتزمين بحضور أكثر من 25% من المحاضرات في مساق معين وكذلك إمكانية تصدير هذا الكشف الى اكسيل لطباعة تقرير لسحب الطلاب من المساق. </li> <li>إمكانية البحث عن كشف محاضرة معينة باستخدام عنوان المحاضرة. </li> </ul> أنواع مستخدمي النظام: <ul style='text-align:justify;direction:rtl;font-family:Traditional Arabic;font-size:24px'> <li>مدير النظام الذي يمكنه (إنشاء وتحرير مساق + إنشاء وتحرير حسابات مستخدمين + ربط المعيد بالمساق التي يعمل عليها). </li> <li>المعيد الذي يمكنه (تسجيل وتحرير طلاب + إضافة وتحرير محاضرات + تسجيل الحضور + استخراج التقارير والكشوفات).</li> </ul> Remember that the manager who defined the specifications is not a software architect so the specifications should not be viewed as necessarily being precise and complete. Also, to keep the project within bounds, we'll ignore issues of ‘auto correction’, ‘full text search’, and application level authorization. # Client Requests <ul style='text-align:justify;font-family:Times New Roman;font-size:18px'> <li>E-R Model</li> <ul> <li>Construct an E-R diagram representing the conceptual design of the database. Be sure to identify primary keys, relationship cardinalities, etc.</li> </ul> <li>Relational Model</li> <ul> <li>After creating an initial relational design from your E-R design, refine it based on the principles of relational design (Chapter 6 & 7).</li> <li>Create the relations in PostgreSQL database, and provide your SQL.</li> <li>Create appropriate constraints, indices, and triggers if you deem necessary, and provide your SQL for those too.</li> <li>Create appropriate users or roles you deem necessary, and provide your SQL for those too.</li> <li>If you discover flaws in the E-R design and fix them, you should also change your Relational Model to match that change. Your final E-R design must be consistent with your final relational design.</li> </ul> <li>Populate Relations</li> <ul> <li>Before you send the project to the client, or even before you proceed into developing the interfaces, you should test the database you implemented. To test it properly you should include enough data to make answers to your queries interesting and non-trivial. Regarding data generation; for simplicity, it is not required perfectly realistic data. So, you don't have to ensure that, for example person names are accurate or lectures are spaceproperly. However, you should strive for a degree of realism that makes showing the project to others -your brother for example- easy and possible.</li> <li>You are free to write a program to generate test data. Make sure to include it in your project submission though.</li> <li>Note the comments on collaboration below; sharing data with others is acceptable as long as appropriate credit is given to your source. </li> </ul> <li>Queries</li> <ul> <li>You should run a number of test queries to see that you have created and loaded your database in the way you intended. The queries listed below are some of those that your clients may find of interest. Such queries may provide further hints about database design, so think about them at the outset of your work on this project.</li> <ul> <li>Display the list of students who attended less than 25% of lectures.</li> <li>What are the top 10 most attended lectures of all time?</li> <li>For each student who attended more than 80% of all lectures, show the lectures he or she did not attend.</li> <li>Display the students ordered by their ‘commitment’ from the most committed to the least. (commitment = الالتزام في الحضور)</li> <li>What are the lectures that had more students missing that lecture than actually attending it? (if a student joined after that lecture, we should not count him as absent) (زاد فيها عدد الغياب عن عدد الحضور)</li> <li>Display the list of students who missed 3 consecutive lectures.</li> </ul> </ul> <li>Interfaces</li> <ul> <li>Customers need an elegant interface to use your system. However, for this project, very simple functioning interface will suffice if your GUI skills are not up to the challenge (After all, this is a database course). These interfaces can be built as a standalone Java application using Swing, JavaFX, JavaFX FXML, or whatever you find yourself comfortable with; even if you'd like to use another web or programming language. </li> <li>You may decide to include in your system an application that runs periodically to do some tasks or something you deem necessary. You may implement such functionality as command line programs (no need for a GUI for that)</li> </ul> <li>Concurrency</li> <ul> <li>The client will not be happy if your project cannot support more than one employee using the system at a time. So, be sure to implement a software that can support multiple concurrent users. Be sure to use the proper transaction mechanism to providing the needed guarantees if a certain task needs that.</li> <li>You can test concurrency by running many applications in separate sessions (make sure you run multiple JDBC connections).</li> <li>Of course, it will also help if you can get some helping hands and simulate the reallife operation.</li> </ul> </ul> # Deliverables Only one submission per one (or two) student(s) is graded. Usually, we wait for the deadline and then start grading your projects. <ul style='text-align:justify;font-family:Times New Roman;font-size:18px'> <li>The final version of the project can be turned in one of two ways:</li> <ol> <li>As a single .zip file on our Moodle.</li> <li>As private GitHub repository you share with us (ozayan@iugaza.edu.ps, rkehail@iugaza.edu.ps, amahdi@iugaza.edu.ps).</li> </ol> </ul> Wither it was a .zip file or a repo, your submission must include: <ol style='text-align:justify;font-family:Times New Roman;font-size:18px'> <li>E-R diagram, plus any explanatory notes. At minimu, you must include all the entity and relationship sets implied by the description. You may go beyond the minimum. Use any tool (e.g. Paint, Microsoft Visio, PlantUML) to make this drawing readable and nice.</li> <li>Relational schema, as a single SQL file that includes all DDL queries to create your database, and any other database objects, users, or roles. It must be something that can be use easily and run on tutor's PostgreSQL.</li> <li>Sample test data, as a single SQL file that allows Eng. Usama and Eng. Rasha to import your database including the data to my PostgreSQL.</li> <li>A set of sample queries, as a single SQL file that includes clear comments followed by your SQL query answering that comment.</li> <li>Your Java code to implement the various interfaces. As mentioned above, quite basic interfaces are acceptable, but it is encouraged to have more elegant interfaces.</li> <ul> <li>If you can submit your java code as .java files that we can compile and run on our side will be the best approach. If your code is dependent on an IDE, please only use IntelliJ IDEA. Also, make sure your configuration does not require us to change my local settings (thank you).</li> <li>You should also tell me how to run your code, continue reading below.</li> </ul> <li>A README file in the top-level folder that explains what is in the project files, and where to find the documents, ... etc. including the usage instructions for the interfaces, and how to import your project and run your code. Your final grade will take a big hit if we cannot run your code due to insufficient instructions (Lackness of necessary information in the user manaul is a pitfall you shall avert in this project).</li> </ol> Everything should be in a single zip file/repo so that when we unzip/clone it, we can read the README file, follow the directions, and run your project. # Grading You will get a grade out of 100, the individual components of your project have the following weights: | Requirement | Points | | ----------- | ------ | | ER design.|20%| | Relational design, including roles, constraints and indices, etc.|15%| | Data creation: sufficient quantity, reasonable realism, sufficiently interesting.|5%| | Sample Queries: sufficiently useful or interesting.|5%| | User interfaces, including proper features, proper updating of the database, etc.|50%| | Concurrent operation of interfaces.|5%| Eng. Usama and Eng. Rasha reserve the right to give extra points for outstanding solutions and presentation to parts of/or the whole project, where Dr. Ahmed will consider in this course evaluation. # Collaboration rules <ul style='text-align:justify;font-family:Times New Roman;font-size:18px'> <li>Your project design and interface implementation must be your own work.</li> <li>You may share data to load into your database. You may also share code that generates those data. Please credit your source in such cases.</li> <li>You may not share your E-R design with others, otherwise you may miss the frontier.</li> <li>You may not share your SQL solutions of the queries, but you may share some sample queries with your colleagues.</li> <li>Students from different groups are not allowed to share the same code, or the same code skeleton, any group must start the project, and code the project by themselves.</li> </ul> ###### tags: `Database Systems` `Projects` `IUG` `Computer Engineering` <center>End Of Project 01</center>