تصميم قاعدة بيانات

رجاءا اقرا تفاصيل المشروع جيدا قبل تقديم اي عرض

Suppose that you have been hired as a database designer to design a

database application system for ATM (Automatic Teller Machine) services of a

bank in the Kurdistan Region of Iraq.

The system must be able to accept Visa and Master cards, both in debit and

credit formats. The currency can be IQD, USD, and EUR. The ATMs are located

in different locations in different cities across the Kurdistan region and also other

parts of Iraq. The cards can also be used through POS (Point of Sale) systems,

online (the Internet-based), and other ATMs all over the world.

The card can be blocked from doing any transaction by a customer request,

according to any legal request to the bank, or by bank decision because of

breaching the usage rules or because of the unsettled awarded credits on the

due time.

The card also can be blocked or automatically taken by the ATM due to

security reasons, for example, trying a wrong password beyond the threshold

number, or because the card has been reported as a stolen or compromised

card.

The functionalities of the system are listed below:

• Cash retrieval

• Money transfer to another account

• Purchase (products and services)

• Balance control

• Password change

You are asked to design a database that is able to support the above

requirements.

You have to consider centralized and distributed database designs and

compare and contrast the advantages and disadvantages of both approaches.

Your study must consider the current situation of the infrastructure of information

technology and communication systems in the Kurdistan region and across Iraq

as an influential parameter in your design approach.

4- Database Design

1 -You have to provide an analysis that compares and contrasts between two

choices: centralized database and distributed database designs.

2 -In the case of distributed database design, you have to design a distributed

database based on the Distributed Database Design Methodology. For your

convenience, the rules are summarized below:

2.1 - Use normal methodology to produce a design for global relations.

2.2 - Examine the topology of the system to determine where databases

will be located.

2.3 - Analyze most important transactions and identify the

appropriateness of horizontal/vertical fragmentation.

2.4 - Decide which relations are not to be fragmented.

2.5 - Examine relations on the “1” side of relationships and determine a

suitable fragmentation schema. Relations on the “many” side may be

suitable for derived fragmentation.

3 -You should also consider the issues of distributed database design (e.g.,

fragmentation strategy to achieve Locality of Reference, Improved Reliability

and

4 -Availability, Improved Performance, Balance Storage and Costs, and Minimal

Communication Costs; Allocation strategies and such).

5 -The information security concerns must be considered and implemented. 5.

The following reports should be created using stored procedures. The

stored procedures must accept proper parameters to make the reports

flexible enough to handle the user requests.

5.1 - List of accomplished transactions on a specific account during a

specified time

5.2 - List of locations where a specific card has been used.

5.3 - List of cards that have been used in a specific location during a

specified time.

5.4 - The balance of a specific card.

5.5 - The balance of all cards that belong to a customer.

5.6 - The cards with an unsettled awarded credit amount.

5.7 - 5.7. The list of the cards that would be expired by a certain time.

5.8 - The cards that are blocked from doing any transaction.

5- Using Advanced Features of DBMS

You have to incorporate the following advanced features of MySQL in your

database design:

1 -Views

2 -Triggers

3 -Stored Procedures

6- Coursework Deliverable (What is required?)

Submit the following three items on Moodle by the time that is specified in

Coursework Due Date section (on page 8) :

1 -A short report: includes the requirements and database design. The report

should be word-processed and prepared according to the academic writing

styles (UKH style). It normally should not be more than 6000 words.

2 -A PowerPoint presentation: verbally demonstrate your project and the

working database with a 10 minutes long presentation. There will be a Q&A

session after the presentation.

3 -A Database: contains the implementation of your database (schemas,

relations, stored procedures, views, triggers, sample data, and such). You

have to consider the followings:

3.1 - The database must contain the test data.

3.2 - The database backup is a part of the submission package.

3.3 - The backup must include the test data and all database scripts.

The backup must be created using the administration facilities of

MySQL.

The report should contain details about what the user is expecting from the

proposed database and how it can be obtained. The structure of the report

should follow the following style:

1 -Title Page – University, department, module (code and name), academic

year, semester, project title, team members, lecturer name.

2 -Table of Contents.

3 -List of Figures (if any).

4 -List of Tables (If any).

5 -Introduction - This section must explain the project, the goal, and the

purpose of the project.

6 -Database Design - This section provides the following items:

6.1 - A comparison of two approaches in the design: centralized and

distributed.

6.2 - The strategy that has been chosen and the reasons for choosing

this strategy.

6.3 - The details of the database design.

6.4 - The high-level conceptual schema design (in ER model), the

logical design (model mapping to the relational model, and

normalization), and the physical design (data types, constraints, choice

of index, and such.).

6.5 - Stored procedures, views, triggers that have been used.

7 -Conclusion - This section must summarize what you have found during the

assignment and what have learned from this experiment.

8 -References – The references that you have consulted and cited in your

report.

9 -Appendix – Any other thing that is falling into the above items, for example,

screenshots running quires on the database.

Presentation – In the presentation, you should:

1 -Explain the chosen approach.

2 -Explain the database design.

3 -Demonstrate the capabilities of the database.

4 -Present the above items in 10 minutes presentation. The presentation time

and venue will be scheduled for weeks 12 and 13 of the semester. You will

be informed about the schedule when it is fixed.