الرجاء المساعدة الضرورية في Dfd/uses Case

مطلوب منا نحلل مشروع كبير وإرفاق أمثلة للتقارير والقوائم وغيرها طبعاً باقي أسبوع على تسليم المشروع وإحنا لسه في الصفر الأخ العزيز المشرف (أمجد) حلل لي ERD وباقي الآن DFD وبقية المطلوب لذا أرجو المساعدة منكم يا إخواني الأعزاء..
Housing Department system

Problem Description/Scenario
We have been asked to analyze the data requirements and develop an information system for the University Housing Office. After studying the working of the office, it is found that the University Housing office manages five housing compounds. Each compound has about 50 apartments. Each apartment falls into one of the categories given in the table below.

الجدول مرفق


The housing office receives many applications from staff and students requesting an apartment on campus. The university. To be eligible to apply, students must be graduate students and staff member must be a full time employee of the university.
Each applicant can mark three preferences for the compound and apartment type desired. Based on availability, they will be offered an apartment. Applicants can reject the offer. After three rejections, they will have to apply again. A non-refundable fee is required for each application. Notification for vacating premises is required one month prior to the move-out date. Once an apartment is allocated to a student or staff, they keep information about the residents of each apartment. For each resident, we keep his CPR number, name, gender, marital status, college, telephone number, and department of the family head. Note that in each apartment more than family members of a staff or student may live. We also want to keep the information about when a staff/student starts living and when he leaves the apartment. For each applicant (i.e. a student or a staff who has applied for housing but has not yet been
assigned an apartment), we store CPR number, name, gender, address, telephone number, marital status, college, department, preference about the village (the name of the village they want to stay in), preference about the apartment (single bedroom, double bedroom, furnished, unfurnished, etc.), etc.
For each compound, we need to store compound number, block number, road number and area. The information about apartment include apartment number (note you can have the same apartment number in different compounds), number of bedrooms, air-conditioning status (central AC, window unit AC, or no AC), and furniture status (furnished or unfurnished).
Tasks to be done
You are required to do the following tasks:
1. Define the problem of the above system assuming it is a manual system.
2. Write all the functional and functional requirements of the above system.
3. Draw the DFD, ERD, or UML of the above system.
4. Design and implement the main menu to mange the above system.
5. Design and implement all the screens for data entry and reports, according to the descriptions of all entities of the system.
6. Examples of reports:
a. Prepares a list of all the apartments that will be available form a given date. The list provides details about the address, air conditioning status, furniture status, etc. of all the apartments available.
b. List the residents of each apartment.
c. Provides a list of sort the information about the applicants based on their priority level.
d. List all those applicants who have applied for a specific apartment type in a specific compound.
e. List all the apartments in different compounds sorted by their type.
f. List the number of apartment that are allocated and that are not allocated to any one.
g. Display details of all the residents living in all the compounds sorted by compound number and apartment number.

You can enhance the above requirements by adding any new reports or screens.

/*==============================================================*/
/* Table: compound */
/*==============================================================*/
create table compound (
compound_number NUMBER not null,
block_number NUMBER,
road_number NUMBER,
area NUMBER(8,2),
constraint PK_COMPOUND primary key (compound_number)
);

/*==============================================================*/
/* Table: apartment */
/*==============================================================*/
create table apartment (
compound_number NUMBER not null,
apartment_number NUMBER not null,
apartment_number_of_bedrooms NUMBER not null,
apartment_air_conditioning_sta CHAR(1) not null
constraint CKC_APARTMENT_AIR_CON_APARTMEN check (apartment_air_conditioning_sta in ('C','W','N')),
apartment_furniture_status CHAR(1) not null
constraint CKC_APARTMENT_FURNITU_APARTMEN check (apartment_furniture_status in ('Y','N')),
apartment_Single_Double CHAR(1) not null
constraint CKC_APARTMENT_SINGLE__APARTMEN check (apartment_Single_Double in ('S','D')),
constraint PK_APARTMENT primary key (compound_number, apartment_number),
constraint FK_APARTMEN_REFERENCE_COMPOUND foreign key (compound_number)
references compound (compound_number)
);

/*==============================================================*/
/* Table: applicant */
/*==============================================================*/
create table applicant (
CPR_number NUMBER not null,
applicant_name VARCHAR2(50),
applicant_gender CHAR(1) not null
constraint CKC_APPLICANT_GENDER_APPLICAN check (applicant_gender in ('M','F')),
applicant_marital_status CHAR(1),
applicant_college VARCHAR2(30),
applicant_telephone_number VARCHAR2(12),
department_of_the_family head VARCHAR2(12),
student_graduate_year date,
staff_member_full_time CHAR(1),
constraint PK_APPLICANT primary key (CPR_number)
);

/*==============================================================*/
/* Table: preference */
/*==============================================================*/
create table preference (
preference_NO NUMBER not null,
CPR_number NUMBER not null,
name_of_the_village VARCHAR2(50),
number_of_bedrooms NUMBER not null,
air_conditioning_status CHAR(1) not null
constraint CKC_AIR_CONDITIONING__PREFEREN check (air_conditioning_status in ('C','W','N')),
furniture_status CHAR(1) not null
constraint CKC_FURNITURE_STATUS_PREFEREN check (furniture_status in ('Y','N')),
Single_Double CHAR(1) not null
constraint CKC_SINGLE_DOUBLE_PREFEREN check (Single_Double in ('S','D')),
application_status CHAR(1),
constraint PK_PREFERENCE primary key (preference_NO, CPR_number),
constraint FK_PREFEREN_REFERENCE_APPLICAN foreign key (CPR_number)
references applicant (CPR_number)
);

/*==============================================================
Table: resident
==============================================================
create table resident (
resident_NO NUMBER not null,
CPR_number NUMBER not null,
compound_number NUMBER not null,
apartment_number NUMBER not null,
start_date DATE,
leave_date DATE,
constraint PK_RESIDENT primary key (resident_NO),
constraint FK_RESIDENT_REFERENCE_APPLICAN foreign key (CPR_number)
references applicant (CPR_number),
constraint FK_RESIDENT_REFERENCE_APARTMEN foreign key (compound_number, apartment_number)
references apartment (compound_number, apartment_number)
);

تم تعديل 29 يونيو 201015 سنة بواسطة qassim313