Database Design and Implementation for Software Projects - Eagle Soars

This assignment focuses on designing a database for a software development company, Eagle Soars Inc. The solution involves identifying entities like projects, users, bug reports, and software updates, along with their attributes. SQL statements are provided to create tables with referential integrity in a Microsoft Access database. The assignment covers creating the database, executing SQL statements to create tables and indices, and inserting sample data into each table. The database design addresses project management, user subscriptions, bug tracking, and software update processes. The solution provides a comprehensive understanding of database design principles applied to a real-world software development scenario.

Eagle Soars, Inc. is a software development firm. They develop specialized mobile software for various
industrial solutions. Their website manages software distribution to their clients/end-users. Each software
development effort is managed as separate project. Each project is defined with a unique 8-letter project
id, can be assigned one or more categories (the categories are Manufacturing, Hospitality,
Pharmaceutical, Automotive, Retail, Construction, etc.) and has a description. Some projects may depend
on other projects and we keep track of the dependency. Each project is developed and owned by a
developer and uploaded to the website in one or more transactions. The end-users are identified by
name, phone number, email, a unique user id and password. They can be either free guest user or a paid
subscription user. They track the subscribers’ date of the subscription. Subscribed users have access to
file bug reports or upload software projects or update patches. Every bug identified has an id (a positive
integer) and a description (text of at most 256 characters). The bug id’s must be unique for all bugs
concerning the same project. The date of filing of a bug report is recorded. Each bug report deals with a
single project and can report a single bug. Each bug report is made by a single subscriber. A subscribed
user can download any project, the number of downloads per user per project is recorded. Some of the
subscribers could also be developers. They develop the software projects and also software updates for
their own projects. Each update or release of a project has an revision id, a name, a status, a description,
and is assigned a particular type (the type are 1, 2 and 3). Each update for a project is created by a single
developer, the one who originally created the project. Each update patch is uploaded to the website in a
transaction. Each transaction has an id and a date when it took place. The transaction id’s must be
unique for all transactions concerning the same project. Requirements: For the above use case, identify
entities (tables) and its attributes (columns) for creating the database and prepare SQL statements to
create tables with referential integrity(FOREIGN KEY) in Microsoft Access Database. Create a Microsoft
Access database and execute each SQL statement to create the tables and indices. Insert at least 10
records of sample data in each table.