In this project, you are tasked with designing a database schema for a module that manages and evaluates hackathon projects. You will create a logical and physical model in third normal form, an Entity$-$Relationship $($ER$)$ diagram for it$,$ develop SQL Server database schemas, and write a SQL query based on the provided project description and requirements.

Project Description:

In the final stages of the hackathon, each project is evaluated by a panel of judges based on established criteria. Projects, each identified by a name, include a detailed description, the source code, and a demo link for further review and archival purposes. The panel is led by a senior judge who coordinates efforts among the committee members. All judges contribute equally to the decision$-$making process, except the senior judge, who has a double vote. Evaluation criteria are comprehensive, covering creativity, robustness, originality, demo quality, societal impact, and the cleanliness and clarity of code. Each criterion is assessed independently on its designated scale. Judges assign scores to each criterion by comparing project features against the defined scale, ensuring a structured and fair evaluation process.

Assignment Tasks:

Data Modeling:

Conceptual Modeling:

Create a list of nouns from the project description.

Determine which nouns will become tables, which will be fields, which will be considered as master data, and which are not used at all.

Submit this list as a text file.

Logical Modeling:

Identify entities and their relationships.

Establish primary keys and create artificial keys as necessary.

Determine relationship types $($one$-$to$-$one, one$-$to$-$many, many$-$to$-$many$).$

Translate many$-$to$-$many relationships into appropriate cross$-$reference tables.

Identify foreign keys.

Create a logical ER diagram in IDEF$1$X notation that reflects all components necessary to represent the project description.

Submit the diagram as $.$png or $.$jpeg file.

Assigning Physical Attributes and DDL Statements:

Write DDL scripts for SQL Server to create the necessary database structures based on your ER diagram. Your scripts should include:

Table creation with fields having specified physical data types, nullability, and constraints $($identity$,$ keys, and check constraints$).$

Primary keys.

Foreign keys.

Unique indexes where appropriate.

The script should run multiple times, that means you need to determine existence and use drop$/$create on the manner described in the prior project's template.

Submit the script as txt or SQL file

Master Data and Initial Entries:

Prepare insert statements for initial data that include:

A set of criteria with scales $(1-10,$ except for demo quality and societal impact, which are $1-5).$

Sample judges, including a senior judge.

Sample projects.

X$-$ref table entries

Use attached files for judges' names and roles and for sample projects to populate the database.

Submit the script as DML inserts file.

SQL Query:

Write a SQL query that will identify the top three highest$-$scoring projects based on the total scores across all criteria. Run it and attach the result as queryAndResult file of $.$txt or $.$SQL extension.

Deliverables:

Text file listing all nouns and their categorizations.

Logical ER diagram in IDEF$1$X notation jpeg or png file

SQL DDL scripts for creating the database schema as txt or SQL file

SQL insert statements for initial data loading as txt or SQL file

SQL retrieve query that finds the top three projects$-$ winners as txt or png file.