Art Gallery Database: Relational Design, Development, and Testing

This report provides a comprehensive solution for designing and developing a relational database system for Shoengalleric Art Gallery, a China-based art gallery facing challenges in managing its growing operations. The database is built using MS Access and includes detailed designs for six interrelated tables: Artwork, Artist, Customer, Artwork Type, Staff, and Upcoming Exhibition. The report covers the entire database development lifecycle, from conceptual design using entity-relationship diagrams to physical implementation with primary and foreign keys. It also demonstrates user interface design through forms, data validation techniques to ensure data integrity, and query implementation to retrieve specific information across multiple tables. The report further includes thorough testing of the database against user and system requirements, along with technical and user documentation to facilitate understanding and maintenance of the system. Normalization principles are applied to minimize data redundancy and ensure data integrity. The report showcases SQL queries, generated reports, and detailed test cases with their respective outcomes.

Database Design and Development

Contents
LO1 Use an appropriate design tool to design a relational database system for a substantial
problem....................................................................................................................................10
P1 Design a relational database system using appropriate design tools and techniques,
containing at least four interrelated tables, with clear statements of user and system
requirements.........................................................................................................................10
LO2 Develop a fully functional relational database system, based on an existing system
design.......................................................................................................................................19
P2 Develop the database system with evidence of user interface, output and data
validations, and querying across multiple tables..................................................................19
LO3 Test the systems against user and system requirements..................................................28
P3 Implement a query language into the relational database system...................................28
LO4 Produce technical and user documentation......................................................................36
P4 Test the system against user and system requirements...................................................36
P5: Produce technical and user documentation........................................................................48
Conclusion................................................................................................................................63
References................................................................................................................................64
Appendix..................................................................................................................................66

List of Figures
Figure 1: Conceptual diagram..................................................................................................12
Figure 2: Relationship Diagram...............................................................................................13
Figure 3: Artwork Datasheet View..........................................................................................19
Figure 4: Artist Datasheet View...............................................................................................20
Figure 5: Artwork type Datasheet View..................................................................................20
Figure 6: Customer Datasheet View........................................................................................20
Figure 7: Staff Datasheet View................................................................................................21
Figure 8: Upcoming Exhibition Datasheet View.....................................................................21
Figure 9: Design View of Artwork Table................................................................................22
Figure 10: Design view of Artist Table....................................................................................22
Figure 11: Design View of Artwork Type Table.....................................................................23
Figure 12: Design View of Customer Table............................................................................23
Figure 13: Design View of Staff Table....................................................................................24
Figure 14: Design View of Upcoming Exhibition...................................................................24

Figure 15: Art Work Form.......................................................................................................25
Figure 16: Artist Form.............................................................................................................25
Figure 17: Artwork Type Form................................................................................................25
Figure 18: Customer Form.......................................................................................................26
Figure 19: Staff Form...............................................................................................................26
Figure 20: Upcoming Exhibition.............................................................................................27
Figure 21: Artist query.............................................................................................................28
Figure 22: Artist SQL view......................................................................................................28
Figure 23: Design view of Artist query....................................................................................29
Figure 24: Art Work query.......................................................................................................30
Figure 25: Artwork query SQL view.......................................................................................30
Figure 26: Artwork query Design View...................................................................................30
Figure 27: Customer query.......................................................................................................31
Figure 28: Customer query SQL view.....................................................................................31
Figure 29: Customer query Design view.................................................................................31
Figure 30: Customer Query 1...................................................................................................32

Figure 31: Customer Query 1 SQL view.................................................................................32
Figure 32: Customer Query 1 Design View.............................................................................33
Figure 33: Artist Query Report................................................................................................34
Figure 34: Artwork Query Report............................................................................................34
Figure 35: Customer Query Report..........................................................................................35
Figure 36: Test case 1..............................................................................................................36
Figure 37: Test Case 2..............................................................................................................37
Figure 38: Test Case 3..............................................................................................................38
Figure 39: Test Case 4..............................................................................................................38
Figure 40: Test Case 5..............................................................................................................39
Figure 41: Test Case 6..............................................................................................................40
Figure 42: Test Case 7..............................................................................................................41
Figure 43: Test Case 8..............................................................................................................41
Figure 44: Test Case 9..............................................................................................................42
Figure 45: Test Case 10............................................................................................................43
Figure 46: Test Case 11............................................................................................................44

Figure 47: Test Case 12............................................................................................................45
Figure 48: Test Case 13............................................................................................................46
Figure 49: Test Case 14............................................................................................................47
Figure 50: Upcoming Exhibition Form....................................................................................48
Figure 51: Artwork Type Form................................................................................................49
Figure 52: Customer Form.......................................................................................................49
Figure 53: Artist Form.............................................................................................................50
Figure 54: Art Work Form.......................................................................................................50
Figure 55: Art Work Form.......................................................................................................51
Figure 56: Staff Form...............................................................................................................55
Figure 57: Art Work Form.......................................................................................................57
Figure 58: Artist Form.............................................................................................................57
Figure 59: Artist Query............................................................................................................59
Figure 60: Artist Query SQL view...........................................................................................59
Figure 61: Art Work Query......................................................................................................60
Figure 62: Artwork query SQL view.......................................................................................60

Figure 63: Customer Query......................................................................................................60
Figure 64: Customer Query SQL view....................................................................................60
Figure 65: Artwork Table Design view...................................................................................61
Figure 66: Artist Table Design View.......................................................................................61
Figure 67: Artwork Table Design View...................................................................................61
Figure 68: Customer Table Design View.................................................................................62
Figure 69: Staff Table Design View........................................................................................62
Figure 70: Upcoming Exhibition Design View.......................................................................62

List of Table
Table 1: Test Case 1 Table.......................................................................................................36
Table 2: Test Case 2 Table.......................................................................................................36
Table 3: Test Case 3 Table.......................................................................................................37
Table 4: Test Case 4 Table.......................................................................................................38
Table 5: Test Case 5 Table.......................................................................................................39
Table 6: : Test Case 6 Table.....................................................................................................39
Table 7: Test Case 7 Table.......................................................................................................40
Table 8: Test Case 8 Table.......................................................................................................41
Table 9: Test Case 9 Table.......................................................................................................42
Table 10: Test Case 10 Table...................................................................................................42
Table 11: Test Case 11 Table...................................................................................................43
Table 12: Test Case 12 Table...................................................................................................44
Table 13: Test Case 13 Table...................................................................................................45
Table 14: Test Case 14 Table...................................................................................................46

Introduction
In this report design of the database and its development is provided on a Shoengalleric Art
Gallery. It is a China-based art gallery and having its groups all over different places. The
gallery has a director that looks after the whole gallery and every branch has its manager that
manages the everyday issues of the gallery. Along with manager the gallery also has a
technician, administrator, preparatory, booking assistant, an assistant for the gallery.
The database will be developed for this project using MS Access. The database involves
some tables that are interrelated and consist of both primary and secondary keys. The data
and its integrity remain maintained in the database. The formed database consists of tables,
forms, relationships and queries so as to determine reports and database. Testing is performed
to analyse the database functionality.

LO1 Use an appropriate design tool to design a relational database system
for a substantial problem
P1 Design a relational database system using appropriate design tools and
techniques, containing at least four interrelated tables, with clear statements of
user and system requirements.
Large problems are faced by Shoengalleric Art gallery situated in China because of its
growing branches and number of increasing employees. Issues are arising in maintaining and
managing all details regarding the people, customers, upcoming exhibitions and artwork
notices. It is really overrated to maintain information in form of paper and pen leading to
burden and it’s better to switch towards the some newly introduced computerized way to
record the details that is a database. The database of this art gallery is created using MS
Access (Astrahan, et-al., 1976).
Role of Database System
The objective of creating a database system is to develop the relationship between tables. It
stores information in a tabular format that is easy to be maintained and information can be
extracted in a simpler way. Some database roles examples are:
 Backend application: Here database is used to keep a proper track of the stored data.
 E-Commerce application: Here database is designed to maintain the records of
customers their categories, subcategories, orders, and products purchased by
customers.
 Data mining application: Reports of accident and disaster occurred are required to be
maintained in form of database (Connolly and Begg, 2005).
System and user requirement

System Requirement
It includes some hardware and software related needs for the database:
Hardware requirements
Requirements of the database that can be met by some hardware’s are known as hardware
requirements. It includes:
 64-bit processor
 1024*468 resolution display
 2 GB RAM minimum (Sharma and Kasana, 2010)
Software requirements
The developed database is made on MS Access and its logical and conceptual diagrams are
also created using the software of draw.io or e-draw.
Conceptual database design
The conceptual diagram presents the relationship between different entities and its attributes.
It is also known as an Entity-Relationship diagram.
 Entities: They are the objects of the real world developed as the tables depending on
the functional and user needs.
 Attributes: They are the sub-properties of entities (Ram and Khatri, 2005).