This was the final project for CS425- Database Organization. In this Project, my team and I had to design a home lending database for credit risk. the motivation for this project was that banks have to loan to potential customers and need a way to keep track of their data like loan forms or personal information. this helps the bank decide weather to approve the loan.
This project had many deliverables like designing the outline of what data goes into the database, designing the database schema, normalizing the database, and then making the database. We were then told to make SQL queries(on fake data) in the database. This was a mix of easy queries, and complex queries that join dataframes and columns.
Deliverable 1:
This went through the ERD using Chens notation of the database. This also went through setting business rules to make sure relationhshipes are defined before database is built. other constraints relating to the individual entities and not the relationships are also set. We then went through all the data that the entity would hold and defined the primary and foreign keys.
Deliverable 2:
This went through the logical ERD which showed cardinality and participation between entities. This also went through the different types of attributes that could be present within each entity. This includes composite attributes, derived attributes, inherited attributes. This then also went through defining the schema of the database and define the weak entities too. This also kept same cardinalities, business rules.
Deliverable 3:
We first fixed the problems in deliverable 2. This went through making fake data, uploading onto a mysql databases, and then applying the queries onto the data. This has simple queries and also harder queries iwith joins. the queries are optimized for performance and shown in the deliverable.
Deliverable 4: We first fixed the problems in deliverable 3. This deliverable was to combine the database with java and connects to the database. We then created a UI tha tcan go through the queries in terms of cases. The cases show queries in the UI predefined like in the deliverable 3. Instead of writing sql, you select the case that matches you query. For example, instead of a sql query that gets everyone with active mortgage, you just select the case and get the result after it runs the predefined query in the java code. This is useful in cases where queries need to be saved and ran through with good UI. Useful for filtering by clicking a button instead of qriting code over and over.