Упражнение - Условия

Exercise: EntityFramework Code-First - Databases Advanced - Entity Framework - октомври 2016

 

Материали от темата

Съдържание на документа

Exercises: Entity Framework Code First

This document defines the exercise assignments for the "Databases Advanced – Entity Framework" course @ Software University.

	Gringotts Database

Your task is to create table WizardDeposits using the Code First approach. The table should contain the following fields:

Id – Primary Key (number in range [1, 231-1].

FirstName – Text field with max length of 50 symbols.

LastName - Text field with max length of 60 symbols. Required

Notes – Text field with max length of 1000 symbols

Age – Non-negative number. Required

MagicWandCreator – Text field with max length of 100 symbols

MagicWandSize – Number in range [1, 215-1]

DepositGroup - Text field with max length of 20 symbols

DepositStartDate – Date and time field

DepositAmount – Floating point number field

DepositInterest - Floating point number field

DepositCharge - Floating point number field

DepositExpirationDate – Date and time field

IsDepositExpired – Boolean field

Add several records to the database using Entity Framework. Use the following example of wizard deposit creation and addition to the database.



	Create User	

Your task is to create table Users using the Code First approach. The table should contain the following fields:

Id – Primary Key (number in range [1, 231-1]) 

Username – Text with length between 4 and 30 symbols. Required.

Password – Required field. Text with length between 6 and 50 symbols. Should contain at least:

1 lowercase letter

1 uppercase letter

1 digit

1 special symbol (!, @, #, $, %, ^, &, *, (, ), _, +, <, >, ?)

E-mail – Required field. Text that is considered to be in format <user>@<host> where:

<user> is a sequence of letters and digits, where '.', '-' and '_' can appear between them (they cannot appear at the beginning or at the end of the sequence). 

<host> is a sequence of at least two words, separated by dots '.' (dots cannot appear at the beginning or at the end of the sequence)

ProfilePicture – Image file with size maximum of 1MB

RegisteredOn – Date and time of user registration

LastTimeLoggedIn – Date and time of the last time the user logged in

Age –  number in range [1, 120]

IsDeleted – Shows whether the user is deleted or not

Seed some users in the database. Test if validation of the fields works as expected.

	Hotel Database

Create database and create the following tables using the Code First approach:

Employees (Id, FirstName, LastName, Title, Notes)

Customers (AccountNumber, FirstName, LastName, PhoneNumber, EmergencyName, EmergencyNumber, Notes)

RoomStatus (RoomStatus, Notes)

RoomTypes (RoomType, Notes)

BedTypes (BedType, Notes)

Rooms (RoomNumber, RoomType, BedType, Rate, RoomStatus, Notes)

Payments (Id, PaymentDate, AccountNumber, FirstDateOccupied, LastDateOccupied, TotalDays, AmountCharged, TaxRate, TaxAmount, PaymentTotal, Notes)

Occupancies (Id, DateOccupied, AccountNumber, RoomNumber, RateApplied, PhoneCharge, Notes)

No relationships between tables are required. Make appropriate validations on different fields.

	Sales Database

Create database for storing data about sales using the Code First approach. The database should have the following tables:

Product (Id, Name, Quantity, Price)

Customer (Id, Name, Email, CreditCardNumber)

StoreLocation (Id, LocationName)

Sale (Id, ProductId, CustomerId, StoreLocationId, Date)

The relationships between tables are as follows:

Sale has one product and a product can be sold in many sales

Sale has one customer and a customer can participate in many sales

Sale has one store location and one store location can have many sales

Write a seed method that fills the database with sample data (randomly generated). Configure Entity Framework to run the Seed() method after the database is created for the first time (i.e. only if it's empty). 

Run the application several times to ensure that it seeds sample data only once.

Hint

You can use the following format to design your model classes

Product 

int Id

string Name 

double Quantity 

decimal Price 

ICollection<Sale> SalesOfProduct

Customer 

int Id

string Name

string Email

string CreditCardNumber

ICollection<Sale> SalesForCustomer

StoreLocation 

int Id

LocationName

ICollection<Sale> SalesInStore

Sale 

int Id

Product Product

Customer Customer

StoreLocation StoreLocation

DateTime Date

Remember to initialize ICollection<Sale> in the constructor of the class to avoid null pointer exceptions.

	Hospital Database

Congrats! You were hired as a Junior Database App Developer. But before starting to work you were required to provide some documents such as fit note from your GP. So, you go to him to get it. When you tell him, what do you need and what kind of job you are about to start. He told you that he was just looking for someone to make a software to help him managing and keeping data about his patients. He offered you to give you the fit note for free if you help him. You decided that’s a great opportunity to save 20 leva and go out tonight with friends and also you would expand your portfolio with 1 project.

Your task is to design a database using the Code First approach. The GP needs to keep information about his patients. Each patient has first name, last name, address, email, date of birth, picture, information whether he has medical insurance or not and should keep history about all his visitations, diagnoses and prescribed medicaments. Each visitation has date and comments. Each diagnose has name and comments for it. Each medicament has name. Make sure all data is validated before inserting in the database.

Bonus Task

Make console based user interface so the doctor can use easily the database.

**Bonus Task

The console based user interface is a good start point but almost nobody use them nowadays (except BDZ Passenger Services). So, make a nice good looking graphical user interface for the program.

	User Towns

It’s time to modify the database we created in the 2nd task. Now the user should have born town and currently living in town. The town has name and country where is he placed. Migrate the database with the new schema of the table and make sure no data is lost when updating.

	User Names

Again, it’s modification time this time not so big. Add 2 new properties to the user first name and last name. Also, add one more property FullName that would return the concatenation of first and last name separated by a single space. That property must be generated only when we need it (there is no need to keep it in the database). Migrate the database with the new schema of the table and make sure no data is lost when updating.

	Hospital Database Modification

Your GP bragged around in the hospital about the cool software you made for him. And now the hospital administration wants to modify your program so they can use it too. They want to store information about the doctors (name and specialty). Each doctor can perform many visitations and in each visitation, can be performed by only one doctor. Make the necessary changes in the database to satisfy the new needs of the hospital administration. When migrating to the new database schema make sure no data is lost. If you made some user interface (graphical or not), make changes in it be more adequate for the changes.

**Bonus Task

Make authentication system for doctors. Each doctor should be able to log in with his email and password. When the doctor is logged in he can see only information related about himself (his visitations, the patients he examined, etc.).

	Email Attribute

Go to task 2 and make a validation attribute [Email] that can be used on string properties. The property should check if the value of the property is valid. One email is valid if in format <user>@<host> where:

<user> is a sequence of letters and digits, where '.', '-' and '_' can appear between them. Examples of valid users: "stephan", "mike03", "s.johnson", "st_steward", "softuni-bulgaria", "12345". Examples of invalid users: ''--123", ".....", "nakov_-", "_steve", ".info". 

<host> is a sequence of at least two words, separated by dots '.'. Each word is sequence of letters and can have hyphens '-' between the letters. Examples of hosts: "softuni.bg", "software-university.com", "intoprogramming.info", "mail.s	oftuni.org". Examples of invalid hosts: "helloworld", ".unknown.soft.", "invalid-host-", "invalid-". 

Examples of valid emails: info@softuni-bulgaria.org, kiki@hotmail.co.uk, no-reply@github.com, s.peterson@mail.uu.net, info-bg@software-university.software.academy. 

Examples of invalid emails: --123@gmail.com, …@mail.bg, .info@info.info, _steve@yahoo.cn, mike@helloworld, mike@.unknown.soft., s.johnson@invalid-.



Use that attribute on the previous problems to validate any property containing e-mail address.

	Password Attribute

Make validation attribute [Password] that can be used to validate string properties. The property should check if the value of the property is valid. In the constructor, the password should receive minimum and maximum length of the password. As optional parameters, we should be able to provide whether the password should contain lowercase letter, uppercase letter, digit or special symbol.





Use that attribute on the previous problems to validate any property containing password.

	Get Users by Email Provider

Write program that print all usernames and emails of users by given email provider.

Example

Input

Output

gmail.com

pesho123 pesho@gmail.com

vanko1 vanko1@gmail.com

goshko_n00b gn00b@gmail.com

yahoo.co.uk

penbo pen@yahoo.co.uk

catLady stepheny.p@yahoo.co.uk

abv.bg

No users found with email domain abv.bg

	Count Users with Bigger Pictures

Write a program that count the users with pictures bigger than given width. 

Example

Input

Output

120

4 users have profile pictures wider than 120 pixels

921

1 user has profile picture wider than 921 pixels

999

No users have profile picture wider than 999 pixels

	Remove Inactive Users

Write a program that set IsDeleted field to true for all users that has not been logged in after given date. Print the number of user that has been set as deleted. Then delete all users that have been marked for removal.

Example

Input

Output

12 Oct 2004

10 users have been deleted

10 Jul 2015

1 user has been deleted

01 Nov 2016

No users have been deleted