Database Design & Development for Shoengalleric Art Gallery
Added on 2024-04-26
49 Pages6097 Words126 Views
Database Design & Development
0
0
Table of Contents
Introduction.................................................................................................................................................5
LO1. Use an appropriate design tool to design a relational database system.............................................6
P1 Design a relational database system using appropriate design tools and techniques........................6
LO2 Develop a fully functional relational database system, based on an existing system design.............15
P2 Develop the database system with evidence of user interface, output and data validations, and
querying across multiple tables.............................................................................................................15
P3 Implement a query language into the relational database system...................................................20
LO3. Test the system against user and system requirements....................................................................28
P4 Test the system against user and system requirements...................................................................28
LO4 Produce technical and user documentation.......................................................................................34
P5 Produce technical and user documentation.....................................................................................34
Conclusion.................................................................................................................................................46
Bibliography...............................................................................................................................................47
Appendix...................................................................................................................................................48
1
Introduction.................................................................................................................................................5
LO1. Use an appropriate design tool to design a relational database system.............................................6
P1 Design a relational database system using appropriate design tools and techniques........................6
LO2 Develop a fully functional relational database system, based on an existing system design.............15
P2 Develop the database system with evidence of user interface, output and data validations, and
querying across multiple tables.............................................................................................................15
P3 Implement a query language into the relational database system...................................................20
LO3. Test the system against user and system requirements....................................................................28
P4 Test the system against user and system requirements...................................................................28
LO4 Produce technical and user documentation.......................................................................................34
P5 Produce technical and user documentation.....................................................................................34
Conclusion.................................................................................................................................................46
Bibliography...............................................................................................................................................47
Appendix...................................................................................................................................................48
1
List of Figure
Figure 1: Entity diagram in ERD...................................................................................................................7
Figure 2: Relational diagram in ERD.............................................................................................................7
Figure 3: ERD Diagram.................................................................................................................................8
Figure 4: Design view of art booking table................................................................................................15
Figure 5: After the data enter art booking table view...............................................................................16
Figure 6: Design view of art group table...................................................................................................16
Figure 7: After the data enter art group table view..................................................................................16
Figure 8: Design view of artist table..........................................................................................................16
Figure 9: After the data enter artist table view.........................................................................................16
Figure 10: Design view of art staff table....................................................................................................17
Figure 11: After the data enter art staff table view...................................................................................17
Figure 12: Design view of artwork table....................................................................................................17
Figure 13: After the data enter artwork table view...................................................................................17
Figure 14: Design view of customer table..................................................................................................18
Figure 15: After the data enter customer table view.................................................................................18
Figure 16: Design view of upcoming exhibition table................................................................................18
Figure 17: After the data enter upcoming exhibition table view...............................................................18
Figure 18: Design view of gallery branch table..........................................................................................19
Figure 19: After the data enter gallery branch table view.........................................................................19
Figure 20: SQL view of Query 1..................................................................................................................20
Figure 21: Design view of query 1..............................................................................................................21
Figure 22: Table view of query 1................................................................................................................21
Figure 23: SQL view of Query 2..................................................................................................................21
Figure 24: Design view of query 2..............................................................................................................21
Figure 25: Table view of query 2................................................................................................................22
Figure 26: SQL view of Query 3..................................................................................................................22
Figure 27: Design view of query 3..............................................................................................................22
Figure 28: Table view of query 3................................................................................................................23
Figure 29: SQL view of Query 4..................................................................................................................23
Figure 30: Design view of query 4..............................................................................................................23
Figure 31: Table view of query 4................................................................................................................23
Figure 32: SQL view of Query 5..................................................................................................................24
Figure 33: Design view of query 5..............................................................................................................24
Figure 34: Table view of query 6................................................................................................................24
Figure 35: SQL view of Query 6..................................................................................................................24
Figure 36: Design view of query 6..............................................................................................................25
Figure 37: Table view of query 6................................................................................................................25
Figure 38: SQL view of Query 7..................................................................................................................25
Figure 39: Design view of query 7..............................................................................................................25
Figure 40: Table view of query 8................................................................................................................26
2
Figure 1: Entity diagram in ERD...................................................................................................................7
Figure 2: Relational diagram in ERD.............................................................................................................7
Figure 3: ERD Diagram.................................................................................................................................8
Figure 4: Design view of art booking table................................................................................................15
Figure 5: After the data enter art booking table view...............................................................................16
Figure 6: Design view of art group table...................................................................................................16
Figure 7: After the data enter art group table view..................................................................................16
Figure 8: Design view of artist table..........................................................................................................16
Figure 9: After the data enter artist table view.........................................................................................16
Figure 10: Design view of art staff table....................................................................................................17
Figure 11: After the data enter art staff table view...................................................................................17
Figure 12: Design view of artwork table....................................................................................................17
Figure 13: After the data enter artwork table view...................................................................................17
Figure 14: Design view of customer table..................................................................................................18
Figure 15: After the data enter customer table view.................................................................................18
Figure 16: Design view of upcoming exhibition table................................................................................18
Figure 17: After the data enter upcoming exhibition table view...............................................................18
Figure 18: Design view of gallery branch table..........................................................................................19
Figure 19: After the data enter gallery branch table view.........................................................................19
Figure 20: SQL view of Query 1..................................................................................................................20
Figure 21: Design view of query 1..............................................................................................................21
Figure 22: Table view of query 1................................................................................................................21
Figure 23: SQL view of Query 2..................................................................................................................21
Figure 24: Design view of query 2..............................................................................................................21
Figure 25: Table view of query 2................................................................................................................22
Figure 26: SQL view of Query 3..................................................................................................................22
Figure 27: Design view of query 3..............................................................................................................22
Figure 28: Table view of query 3................................................................................................................23
Figure 29: SQL view of Query 4..................................................................................................................23
Figure 30: Design view of query 4..............................................................................................................23
Figure 31: Table view of query 4................................................................................................................23
Figure 32: SQL view of Query 5..................................................................................................................24
Figure 33: Design view of query 5..............................................................................................................24
Figure 34: Table view of query 6................................................................................................................24
Figure 35: SQL view of Query 6..................................................................................................................24
Figure 36: Design view of query 6..............................................................................................................25
Figure 37: Table view of query 6................................................................................................................25
Figure 38: SQL view of Query 7..................................................................................................................25
Figure 39: Design view of query 7..............................................................................................................25
Figure 40: Table view of query 8................................................................................................................26
2
Figure 41: SQL view of Query 8..................................................................................................................26
Figure 42: Design view of query 8..............................................................................................................26
Figure 43: Table view of query 8................................................................................................................26
Figure 44: SQL view of Query 9..................................................................................................................27
Figure 45: Design view of query 9..............................................................................................................27
Figure 46: Table view of query 9................................................................................................................27
Figure 47: White box testing.....................................................................................................................28
Figure 48: Black box testing.......................................................................................................................29
Figure 49: Test Case 1................................................................................................................................31
Figure 50: Test case 2................................................................................................................................31
Figure 51: Test case 3................................................................................................................................31
Figure 52: Test case 4................................................................................................................................32
Figure 53: Test case 5................................................................................................................................32
Figure 54: Test case 7................................................................................................................................33
Figure 55: Process 1 for open MS Access...................................................................................................34
Figure 56: process 2...................................................................................................................................35
Figure 57: process 3...................................................................................................................................35
Figure 58: Art Booking Form......................................................................................................................36
Figure 59: Upcoming Exhibition form........................................................................................................36
Figure 60: Gallery Branch Form.................................................................................................................36
Figure 61: customer form.........................................................................................................................37
Figure 62: Artwork form...........................................................................................................................37
Figure 63: Art staff Form............................................................................................................................38
Figure 64: Artist from................................................................................................................................38
Figure 65: Art group Form.........................................................................................................................38
Figure 66: Validation case 1.......................................................................................................................39
Figure 67: Validation case 2.......................................................................................................................39
Figure 68: Validation case 3.......................................................................................................................40
Figure 69: Validation case 4.......................................................................................................................40
Figure 70: Properties of upcoming exhibition Forms.................................................................................41
Figure 71: Properties of customer Forms..................................................................................................41
Figure 72: Properties of Artwork Forms....................................................................................................42
Figure 73: Properties of Art staff Forms....................................................................................................42
Figure 74: Properties of Artist Forms.........................................................................................................43
Figure 75: Properties of Art Group Forms.................................................................................................43
Figure 76: Properties of Art booking Forms...............................................................................................44
Figure 77: Properties of Gallery Branch Forms..........................................................................................44
Figure 78: Relationship Diagram using MS Access Database.....................................................................45
3
Figure 42: Design view of query 8..............................................................................................................26
Figure 43: Table view of query 8................................................................................................................26
Figure 44: SQL view of Query 9..................................................................................................................27
Figure 45: Design view of query 9..............................................................................................................27
Figure 46: Table view of query 9................................................................................................................27
Figure 47: White box testing.....................................................................................................................28
Figure 48: Black box testing.......................................................................................................................29
Figure 49: Test Case 1................................................................................................................................31
Figure 50: Test case 2................................................................................................................................31
Figure 51: Test case 3................................................................................................................................31
Figure 52: Test case 4................................................................................................................................32
Figure 53: Test case 5................................................................................................................................32
Figure 54: Test case 7................................................................................................................................33
Figure 55: Process 1 for open MS Access...................................................................................................34
Figure 56: process 2...................................................................................................................................35
Figure 57: process 3...................................................................................................................................35
Figure 58: Art Booking Form......................................................................................................................36
Figure 59: Upcoming Exhibition form........................................................................................................36
Figure 60: Gallery Branch Form.................................................................................................................36
Figure 61: customer form.........................................................................................................................37
Figure 62: Artwork form...........................................................................................................................37
Figure 63: Art staff Form............................................................................................................................38
Figure 64: Artist from................................................................................................................................38
Figure 65: Art group Form.........................................................................................................................38
Figure 66: Validation case 1.......................................................................................................................39
Figure 67: Validation case 2.......................................................................................................................39
Figure 68: Validation case 3.......................................................................................................................40
Figure 69: Validation case 4.......................................................................................................................40
Figure 70: Properties of upcoming exhibition Forms.................................................................................41
Figure 71: Properties of customer Forms..................................................................................................41
Figure 72: Properties of Artwork Forms....................................................................................................42
Figure 73: Properties of Art staff Forms....................................................................................................42
Figure 74: Properties of Artist Forms.........................................................................................................43
Figure 75: Properties of Art Group Forms.................................................................................................43
Figure 76: Properties of Art booking Forms...............................................................................................44
Figure 77: Properties of Gallery Branch Forms..........................................................................................44
Figure 78: Relationship Diagram using MS Access Database.....................................................................45
3
List of Table
Table 1: Table of ERD Relationship..............................................................................................................8
Table 2: 1NF Normalization.........................................................................................................................9
Table 3: 2NF Normalization.......................................................................................................................10
Table 4: 3NF Normalization.......................................................................................................................11
Table 5: Table of Data dictionary...............................................................................................................12
Table 6: Table of test case.........................................................................................................................30
Table 7: table of Validation........................................................................................................................39
4
Table 1: Table of ERD Relationship..............................................................................................................8
Table 2: 1NF Normalization.........................................................................................................................9
Table 3: 2NF Normalization.......................................................................................................................10
Table 4: 3NF Normalization.......................................................................................................................11
Table 5: Table of Data dictionary...............................................................................................................12
Table 6: Table of test case.........................................................................................................................30
Table 7: table of Validation........................................................................................................................39
4
Introduction
The database is a collection of data or records. It is used to manage, update, and access data. Data is
stored table form into the database. Here, I have designed a database for art gallery which is established
in China in 2010. The database is managed by data normalization and data verification and validations.
Shoengalleric art gallery database is a relational database which is created on MS Access. Shoengalleric
art gallery database, the report is divided into four learning outcomes.
The first section, it is defined the user and system requirements for shoengalleric art gallery database.
This section is also designed data normalization, data dictionary, data validation and ER diagram
according to shoengalleric art gallery database.
In the Second section, I have designed a relational database using MS Access. shoengalleric art gallery
database is contained Art Booking, Art Group, Artist, Art staff, Artwork, Customer, Upcoming exhibition,
and Gallery branch. It also includes query which is used for extracted data from the database.
In the third section, there are performed the testing on shoengalleric art gallery database against system
and user requirements through the test case.
In the fourth section, explain user and technical documentation for art gallery database. Also, there are
defined full functional dependencies for shoengalleric art gallery database.
5
The database is a collection of data or records. It is used to manage, update, and access data. Data is
stored table form into the database. Here, I have designed a database for art gallery which is established
in China in 2010. The database is managed by data normalization and data verification and validations.
Shoengalleric art gallery database is a relational database which is created on MS Access. Shoengalleric
art gallery database, the report is divided into four learning outcomes.
The first section, it is defined the user and system requirements for shoengalleric art gallery database.
This section is also designed data normalization, data dictionary, data validation and ER diagram
according to shoengalleric art gallery database.
In the Second section, I have designed a relational database using MS Access. shoengalleric art gallery
database is contained Art Booking, Art Group, Artist, Art staff, Artwork, Customer, Upcoming exhibition,
and Gallery branch. It also includes query which is used for extracted data from the database.
In the third section, there are performed the testing on shoengalleric art gallery database against system
and user requirements through the test case.
In the fourth section, explain user and technical documentation for art gallery database. Also, there are
defined full functional dependencies for shoengalleric art gallery database.
5
LO1. Use an appropriate design tool to design a relational database system
P1 Design a relational database system using appropriate design tools and techniques
Here I have designed database for a Shoengalleric art gallery. The database is designed according to
system and user requirement. Designs of the database are classification into the Logical design, Physical
design, and Conceptual design. The database is created with the help of Normalization because it is
reduced the complexity of the database. Database of the Shoengalleric art gallery is a relational
database. In this section, I have also explained ERD diagram with entities and attributes.
User requirement for case study:
Shoengalleric is an art gallery in chains. It needs a database to store information in the database. The
user needs staffs like as directors, technician, gallery assistant, administrators, preparatory, managers,
and booking assistant where the role is defined for each member of gallery staff. They want to save
customer detail, artist details, artwork detail and booking details. Shoengalleric is also want to reduced
paperwork and increased the communication with help of sharing. Shoengalleric needs a database
which is secure, easily understandable, less complex, and easily accessible.
System requirement for case study:
System requirements: it is different from user requirement because system requirements are depended
on the project. It is defined according to project such as Project Budget, Operating hours, Backups
Schedule, Project Team, Maintenance Schedule, Security( model number of security and Database
auditing ) Testing (it is performed data validations and data verification process on the database ). The
tester may be user, staff, or developer. The shoengalleric art gallery is needed security configuration in
the database. It needs a database which is accessible by directors, technician, gallery assistant,
administrators, preparatory, managers, and booking assistant.
Relational Database:
A relational database is stored data in tabular form. It contained row and column in the table. Relation
database is needed more memory because it is handle number of data and complex queries in the
database. It is a less efficient database which is used in the business database. Relational database
defined a relational between tables or objects (Anon., 2016).
Role of the Relational database:
RDB is used as an intermediate between database and user because it is a collection of files.
In the e-commerce, it is stored the information organize and provide the security and maintains
of the database.
Data mining is used a relational database as relational association rules. relational classification,
and relational regression tree.
6
P1 Design a relational database system using appropriate design tools and techniques
Here I have designed database for a Shoengalleric art gallery. The database is designed according to
system and user requirement. Designs of the database are classification into the Logical design, Physical
design, and Conceptual design. The database is created with the help of Normalization because it is
reduced the complexity of the database. Database of the Shoengalleric art gallery is a relational
database. In this section, I have also explained ERD diagram with entities and attributes.
User requirement for case study:
Shoengalleric is an art gallery in chains. It needs a database to store information in the database. The
user needs staffs like as directors, technician, gallery assistant, administrators, preparatory, managers,
and booking assistant where the role is defined for each member of gallery staff. They want to save
customer detail, artist details, artwork detail and booking details. Shoengalleric is also want to reduced
paperwork and increased the communication with help of sharing. Shoengalleric needs a database
which is secure, easily understandable, less complex, and easily accessible.
System requirement for case study:
System requirements: it is different from user requirement because system requirements are depended
on the project. It is defined according to project such as Project Budget, Operating hours, Backups
Schedule, Project Team, Maintenance Schedule, Security( model number of security and Database
auditing ) Testing (it is performed data validations and data verification process on the database ). The
tester may be user, staff, or developer. The shoengalleric art gallery is needed security configuration in
the database. It needs a database which is accessible by directors, technician, gallery assistant,
administrators, preparatory, managers, and booking assistant.
Relational Database:
A relational database is stored data in tabular form. It contained row and column in the table. Relation
database is needed more memory because it is handle number of data and complex queries in the
database. It is a less efficient database which is used in the business database. Relational database
defined a relational between tables or objects (Anon., 2016).
Role of the Relational database:
RDB is used as an intermediate between database and user because it is a collection of files.
In the e-commerce, it is stored the information organize and provide the security and maintains
of the database.
Data mining is used a relational database as relational association rules. relational classification,
and relational regression tree.
6
A Back-End Database is indirectly accessed by users with the help of external application
(jeancly, 2012).
ER diagram: ERD is a flowchart that represents the relationship between entities. It is also known as ER
Models, ERDs, and entity relationship diagram. It is used a number of a symbol like as rectangles, ovals,
diamonds, and arrows (represent the relational of entity). ERD is representing a grammatical structure
because the entity is used as noun and relationships are used as verbs (Thiru, n.d.).
Entity: It is used to represent the object in the database like person, event, and concept. The entity is
representing as rectangle box. The entity is three types like as weak, strong and associative. The weak
entity has no own attributes but strong entity has contained own attributes. The associative entity is
contained entity set (Lucidchart, n.d.).
Relationship: It is defined the connection between entity. Relationships are represented as diamonds or
connecting line with an arrow. In the ERD, relationships are work as verbs. Weak relationship and strong
relationship are the type of relationship (Lucidchart, n.d.).
Here, I have designed ERD diagram for a Shoengalleric art gallery.
7
Entity
Relationship
Figure 1: Entity diagram in ERD
Figure 2: Relational diagram in ERD
(jeancly, 2012).
ER diagram: ERD is a flowchart that represents the relationship between entities. It is also known as ER
Models, ERDs, and entity relationship diagram. It is used a number of a symbol like as rectangles, ovals,
diamonds, and arrows (represent the relational of entity). ERD is representing a grammatical structure
because the entity is used as noun and relationships are used as verbs (Thiru, n.d.).
Entity: It is used to represent the object in the database like person, event, and concept. The entity is
representing as rectangle box. The entity is three types like as weak, strong and associative. The weak
entity has no own attributes but strong entity has contained own attributes. The associative entity is
contained entity set (Lucidchart, n.d.).
Relationship: It is defined the connection between entity. Relationships are represented as diamonds or
connecting line with an arrow. In the ERD, relationships are work as verbs. Weak relationship and strong
relationship are the type of relationship (Lucidchart, n.d.).
Here, I have designed ERD diagram for a Shoengalleric art gallery.
7
Entity
Relationship
Figure 1: Entity diagram in ERD
Figure 2: Relational diagram in ERD
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents
Database Design and Development for Shoengalleric Art Gallerylg...
|66
|5608
|329
Database Design and Developmentlg...
|58
|10295
|296
The assignment submission is entirely my own work and I understand the consequences of plagiarismlg...
|75
|6901
|487
Database Design & Development Assignment 2lg...
|75
|6962
|200
Data Model Development and Implementation Assignment 2lg...
|27
|2238
|305
Fuel Reporting and Employee Roster Management Systemlg...
|46
|5435
|405