Database Project Requirements Details 526

Summary

In this lecture, Alvin John outlines the comprehensive requirements for a database project that students need to tackle individually. The process begins with selecting a domain area of interest, and involves creating detailed business scenarios, designing databases logically through English queries and ER diagrams, and refining through normalization. Once the logical design is confirmed, students move to physical design using tools like Microsoft Access. The culmination of the project includes creating documentation, a PowerPoint presentation, a video demonstration, and a YouTube video link as part of the final deliverables. The project encourages students to choose applications they are familiar with, ranging from retail databases to CRM systems, ensuring they adhere to specific criteria for the number of queries, tables, and attributes.

Highlights

• Start your project by picking a domain area you're excited about! 🌟
• Design your database with engaging and detailed business purposes. 📈
• List all potential queries in English before diving into ER diagrams! 🧩
• Normalize each table to avoid data inconsistencies and anomalies. 🚀
• Finish strong with a documented presentation and an online demo showcase! 🎬

Key Takeaways

• Choose a domain you love to model databases efficiently! 🛍️
• Get creative with business scenarios to nail your database's purpose! 🎯
• Logical design starts in English—then transforms into a magical ER diagram! 📊
• Normalization is your friend! Eliminate data duplicates for a clean slate! ✔️
• Complete your masterpiece with a YouTube demo and PowerPoint presentation! 📽️

Overview

This project kicks off with the thrilling task of selecting a domain area, such as a bookstore or CRM system, that sparks your interest in modeling a database. You're encouraged to dream up detailed business scenarios, pondering questions like helping customers find books or managing customer relationships to set the stage for your database's heroic journey. 🤓

As you design, start by crafting English-language queries, leading you to build your ER diagrams—think of it as sculpting your masterpiece! 🎨 This phase involves identifying tables, attributes, keys, data types, and ensuring every part of your database cosmos is connected through relationships. Normalizing tables is your next move, perfecting them into a harmonious data symphony by eliminating redundancies. 🎼

Your journey doesn't stop at logic—venture into the physical realm using tools like MS Access. Create and connect tables, establish referential integrity, and test your database through rigorous query battles to ensure everything works seamlessly. 🌐 Conclude by documenting your gallant efforts in an engaging PowerPoint presentation, spicing it with a demo recorded on YouTube. Show your prowess and present these with a shining Dropbox submission! 📂

Chapters

• 00:00 - 00:30: Introduction and Overview The chapter introduces a database project requirement for the class. This project is mandatory for everyone and must be done individually. The instructor emphasizes the significance of this approach and begins to elaborate on the project details.
• 00:30 - 01:30: Selecting a Domain Area The chapter stresses the importance of selecting a domain area before starting a database project. It suggests that one should choose a domain that they are most interested in for modeling database applications.
• 01:30 - 03:00: Building a Detailed Scenario The chapter 'Building a Detailed Scenario' emphasizes the importance of thoroughly understanding the domain area, specifically using bookstore applications as an example. It suggests that the domain chosen for examination should align with personal interests to facilitate more in-depth knowledge. Key operational aspects such as assisting customers in finding books are highlighted as critical elements that need support within the bookstore scenario.
• 03:00 - 07:00: Logical Design Phase This chapter discusses the Logical Design Phase with a focus on handling specific queries in an application. The text mentions the need to accommodate questions about books in specific areas, inquiries about authors, and lists of books authored by specific individuals. It emphasizes the importance of considering these questions during the logical design process and deciding the appropriate application domain for implementation.
• 07:00 - 09:30: Refining the Logical Design The chapter titled 'Refining the Logical Design' discusses the importance of clearly stating the purpose of each application within a business context. It emphasizes constructing detailed scenarios that outline the specific business purposes of these applications. The chapter suggests describing, in moderate detail, how various business functions are performed, providing a framework to refine the logical design of applications in alignment with business needs.
• 09:30 - 14:00: Physical Design Phase In this chapter, the focus is on the Physical Design Phase where various operational scenarios are discussed. It is essential to understand how the business operates, including identifying key personnel, suppliers, and potential customers. This phase involves considering the numerous elements that support business operations and recognizing the benefits that the new system will provide. The role of each stakeholder and their contribution to the system's implementation is also emphasized.
• 14:00 - 17:00: Final Documentation and Presentation The chapter, titled 'Final Documentation and Presentation,' focuses on outlining the technical constraints that may arise when completing a project. It emphasizes the importance of identifying any technical limitations related to resources and proposes strategies or methods to support these technical needs effectively. This involves carefully planning and structuring the necessary documentation to ensure clear communication of the technical capabilities and restrictions. Moreover, the chapter likely addresses how to prepare and present these findings and constraints in a final presentation, ensuring all stakeholders have a shared understanding of the project's technical framework. The discussion seems to stress the role of strategic support and comprehensive documentation in overcoming technical hurdles and facilitating successful project completion.
• 17:00 - 19:00: Scope of Applications The chapter covers the initial steps in database design, emphasizing the importance of domain selection and scenario detailing. It describes the transition from domain analysis to the logical design phase, where the creation of an English-list of potential database queries is crucial. This step involves drafting all possible questions that the database needs to address in relation to business requirements, highlighting the focus on practical application and strategic planning within database management.
• 19:00 - 19:30: Individual vs. Group Project Requirements This chapter discusses the essential requirements for individual and group projects. It emphasizes the need to list requirements in any order, preferably in English, as a preliminary step. From these requirements, an ER diagram and relationship diagram can be constructed. To create a proper diagram, it's crucial to first identify the tables, which equate to specific subject areas for storing facts. The chapter outlines the importance of deciding on specific subject areas that need to be documented.

Database Project Requirements Details 526 Transcription

• 00:00 - 00:30 All right. Uh in this lecture I want to talk about a database project requirement. Uh the database project is requirement for everybody and uh it is a project uh that needs to be done as an individual project. uh for this class and I specifically want it to be done by an individual and um I want to talk about
• 00:30 - 01:00 what are the requirements and how the database project is to be initiated and uh and progressed and what the final requirements are and so forth. All right. So first of all um what you should do is you should select a domain area. domain area. In other words, what are the area that you are most interested in modeling database applications? Uh for example, if you're interested in uh dealing
• 01:00 - 01:30 with uh bookstore applications, then uh that domain area uh needs to be examined in detail. And hopefully that area is the area that you are most interested in. So you have some idea of how bookstore operations are done. What are the things that needs to be supported? For example, uh helping customers to find uh a book uh with
• 01:30 - 02:00 specific information or a list of books that are in certain areas or you know some questions on uh specific author who wrote a book, what are the you list of books written by author and so forth. There may be multiple questions that uh needs to be handled. So those things needs to be uh taken into consideration when you do that and then uh decide the uh an application domain and then you'll
• 02:00 - 02:30 have to state its purpose. All right. And and that is done by building a detailed scenario of that business in terms of what are the specific business purposes for the applications. So you should somehow describe in moderate details how the businesses are performed in two three
• 02:30 - 03:00 paragraphs and then operational scenarios how the businesses are the businesses operated who are involved who are personnel who are involved who are the suppliers who will be potential customers who will um benefit from this system you know so forth. There could be many things that u that are involved in supporting the operation of the uh of the business and then also you can
• 03:00 - 03:30 um provide uh some constraint on technical uh capabilities technical constraint. So if it it is to do with some technical um uh resources then you should think of how to support that technical uh resources to be
• 03:30 - 04:00 handled. And then once you um decide the domain area and uh and have a detailed scenario then next you can perform logical design of database by building uh first of all a list of database queries in English. So that is basic English queries. You provide all potential questions that are to be used in the business. So you can
• 04:00 - 04:30 list them um in any order. You can list them in and write them in English and list them. And then based on that you may build an ER diagram and the relationship diagram. So to build your diagram you should have first of all identify the tables uh which means specific subject areas that you need to store facts about and then also you should decide
• 04:30 - 05:00 what attributes are needed for each table. So each attribute basically refers to fact a fact that you need to store about each subject. So you have to decide on the table and then decide how many and what attributes are in the table and then for each table you have to decide primary key that will be used to uniquely identify the records in the table. Every table should have a primary key
• 05:00 - 05:30 designated and for keys are needed when you um combine or connect to another table. um through a relationship. So that also can be specified um on the other uh table that is connected to and then you have to also identify and define data types for each attribute. So every attribute you have to specify data type so
• 05:30 - 06:00 that consistent and correct values are given and then also uh between all the tables that you create all the tables must be connected to relationships. All right. So if you have multiple tables you know let's say you have three tables like this then you have to connect them you know either this way whatever way um you want to connect but you have to connect the relationships. So so building a eer
• 06:00 - 06:30 diagram and and a list of queries uh is a a starting point of logical database design. Of course when you build um database queries you have to think of later on you know you'll have to convert this to during physical design stage you have to convert this into um SQL queries later on but at the logical design stage you may just build a list of database queries in English.
• 06:30 - 07:00 All right. So logical design basically means you define a query in English and then based on the queries you have draft E diagrams with all the necessary information such as tables, attributes, keys, data types and relationships. Next point is for you to refine logical design. All right, refine the logical design of database. Okay. So
• 07:00 - 07:30 the one the logical design that you completed the step two uh will be in the form of E diagram and that will give you a table structure because it it'll define a table um with attribute and then data types and key and and primary key and so forth. And then you have to define normalize each table to be in three and f third normal form. Uh and third normal
• 07:30 - 08:00 form will be topic of uh chapter 5 which uh will be done um after midum exam. But normalize each table normalizing each table is next step. uh normalization basically uh is to help eliminate the duplication of uh the data in the table and therefore it'll potentially decrease the uh the
• 08:00 - 08:30 inconsistency um that may exist in the in the table when update table update is done. So we'll talk about that uh in later point but the important point here is that during the logical design refinement stage you'll have to normalize each table so that all the tables in the database will be in 3DNF format and then uh between
• 08:30 - 09:00 um two tables when they are connected through one to many or many to many uh make sure that you enforce reference integrity rules because that was already explained but this is to make sure that uh the um anomalies uh when deletion occurs anomalies when insertion occurs will be eliminated. So insertion anomaly, delete anomaly or update anomaly um can be uh
• 09:00 - 09:30 enforced uh and um done through uh reference integrities and then after that uh after logical design is completed. So at this stage and your first step is done. All right. So at by this time you your logical design is completed and then step four and onward you perform physical design or a
• 09:30 - 10:00 PD which means now first of all you have to select right tool. I suggest you use Microsoft Access or any kind of SQL that you have or maybe Oracle if you have um experience if you have knowledge of Oracle or any anything that supports relational databases will be fine but mostly I suggest that MS uh Microsoft access which is easiest uh tool that is available uh through Microsoft
• 10:00 - 10:30 Office and then uh once you select the tool you'll have to create tables and then create relationship and build referential integrities and you actually do this using the tools. So this is how you basically um how you build and then of course you have to uh write the queries over. Of course here next step here I have did not include here is you have to create
• 10:30 - 11:00 tables create relationship and then you have to build the queries. Okay. So uh create tables create relationships build referential inter and then create the queries. All right. So this part so you have to create the queries. So that is basically um main part of physical design main part of physical design those part entire part including um creating queries and
• 11:00 - 11:30 then and then once you've done that you have to test and refine right how do you test and refine? Well, once you create the table, you have relationship reference and queries and run the queries. See if you get what you want as a result. So you run all the queries that you start uh you started with when you design the databases at the beginning and make sure that you get what you what you want and if there's
• 11:30 - 12:00 any problems uh you notice or if you feel that there is um there is a um the changes to be done or you know different um modification needs to be done and then you refine them as needed. define them as needed. All right. So that is basically up to this part is physical design. All right. And
• 12:00 - 12:30 next step is for you to document the work. In the document you have to include title page which is title of the project. State the purposes why you are doing this and detailed operation scenarios. I discussed this. You have to provide what are the how what kind of operations are done who are involved what are the you know what are the business um doing what nature what are the conditions and constraint budget
• 12:30 - 13:00 constraint technical constraint so forth so write about operational scenarios and then you show the query list uh in terms of English and SQL Well, both and also include answers resulting from the execution of queries and then you have to include
• 13:00 - 13:30 er diagram which is logical design. Also you have to include also the contents of the table. Also you have to show the what kind of relationship between and among the tables and then show any referential integrities that are enforced. So you have to include all these as a document. All right. All these as a document for the project. Okay. After that uh as a final requirement you'll have to prepare a
• 13:30 - 14:00 PowerPoint presentation including everything I have talked about so far for the project and then do the demo of query execution recorded and then prepare a YouTube video that includes both PowerPoint presentation and the demo and then create a link to it. Right. So basically PowerPoint
• 14:00 - 14:30 presentation and demo presentation for query execution and put them together and uh create a YouTube uh video to show uh your PowerPoint presentation that you do and then uh your own demo. Okay, you may want to show your face as well when you do the demo briefly to make sure that I want to make sure that that's you you know who you are doing and uh and also make sure that you are actually presenting it. And then final
• 14:30 - 15:00 deliverable is is the document. Okay. All document from step six which is this. All of these in word and then YouTube link right here. YouTube link from step seven and these two you put in a Dropbox called final project requirements. Okay. So Dropbox and the
• 15:00 - 15:30 final project requirements by the due date which I'll announce it later. All right. So that's final requirement for your project and then uh I want to talk about scope of applications. The kind of application you'll be considering could be various. You could choose basically anything. For example, you may choose retail retail point of sales database.
• 15:30 - 16:00 You may choose HR human resource database. You may choose maybe power or all of customer relationship management CRM package. Then normally through this you know this package uh people track customers and they ma and manage the sales cycle. You may also choose to do bookstore management systems or maybe hotel reservation system maybe online retail store management system or anything like this. So these are some examples. So you can think of some kind
• 16:00 - 16:30 of applications that you are interested in or application that you may uh you may be familiar with or you know any any application that you have enough information about you can choose that uh and then um again like I said in choose the domain uh that you know very well or you are familiar with. All right. Then basically you need more details about why you are creating the
• 16:30 - 17:00 database. Um because you need the op you need operational scenario and you need the detailed information for you to be able to create tables and attribute and relationships and and and referential integrities. All right. So please keep that in [Music] mind. And the additional scope of application for the projects as follows here as you can see um there are two types of project either individual or group project but for this course um
• 17:00 - 17:30 mostly I think is individual project but for if it is individual project the number of queries are uh 10 to uh 15 queries whereas the group project has to be um the 15 to 20. If it is group project basically students um of size two or three student at most right so um uh basically so that is the uh general criteria for number of queries number of tables for individual project
• 17:30 - 18:00 five to seven tables um if you go be more than seven that's fine but minimum five for the group project number of table has to be seven to 10 or more than 10 is fine as Right. Size of table number of records in the table. Number of rows in the tables for each table minimum 15 to 30 and for the group project 20 to 50. And the number of attributes. So number of columns in other words for the
• 18:00 - 18:30 table five to seven per table for individual project and five to 10 per table for group project. So please keep that in mind uh when you design your database. And I think that's it for the project requirements.